Weighting questions - Calculator

This question isn't specific to any Articulate products, but I knew that this community is full of awesome heroes, so I figured I'd give it a shot.

Does anyone know of an Excel spreadsheet or other calculator that can help me weight questions?

Extremely simple example (for clarity): I am given a 100-question quiz, but 25 questions must be weighted to be worth 2 points. I need to know how to scale the score of the other 75 questions quickly and still equal 100.

Obviously, the example above would be easy to calculate, but what if I have an 87-question quiz, 14 questions need to be weighted at 2 points, 5 questions at 3 points, and the rest need to take up the slack... still equaling 100.

This seems like something that could be done in Excel, but I don't have the gray-matter to create such a formula.

 

3 Replies
Jason Reed

Hi Daniel,

There may be a way to set up an excel sheet to automatically do this for you, but you can achieve it with a little bit of trial and error. 

If we take your 100 questions each would be worth 1 point to start with. I'd put a value of one in each cell in excel and do a sum of that column to equal 100.

Then go through and weight each of 25 questions with 2 points. You'll now have a total of 125.

Now I'd do another formula in a nearby cell that subtracts 100 from the total. The purpose of this is to show how much over your total you are. In this case you should have 25.

Now a third formula divides those 25 extra points by 75 (the number questions left at one point).

Finally, you'll just need to subtract that value from each of the 1 point questions to equal 100. You might need to do a little rounding up.

I realise this sounds complicated. I've created the excel file and attached it for you to play with. I've also applied some conditional formatting so you can easily see which questions are 1 or greater than 1.

Best of luck and feel free to ask any questions if you're stuck.