Thread: Calculating a score in Excel

I have an Excel spreadsheet that I'm using to calculate a score based on how the evaluator rates a certain area. The area can have a score between 1 through 5 with 5 being the best. There are at times multiple evaluators. So for example there are eight evaluators, two of them rated the area "1" and the other six rated the area "2". The method I'm using to sum up the scores and output an overall average of the area, I'm getting higher numbers that I should be. The above example with my method is giving me a score of 2.8

Just by using my head, I believe the score should be in the 1.7-1.8 range. The score should not be higher than 2 since there were no 3s scored. This is the formula I'm using for Excel:

Code:

 
=IF(B1>0,SUM(B1)*0.2,0)+IF(B2>0,SUM(B2)*0.4,0)+IF(B3>0,SUM(B3)*0.6,0)....

BX contains the number of instances for that particular score, B1 refers to a score of 1. Help would be appreciated, thank you!

So I figured out the proper score, 1.75. If you use AVERAGE(1,1,2,2,2,2,2,2) you get 1.75, which I believe is the proper score. Now the hard part is figuring out how to get Excel to manipulate the raw data into such a formula.

Originally Posted by spin360

So I figured out the proper score, 1.75. If you use AVERAGE(1,1,2,2,2,2,2,2) you get 1.75, which I believe is the proper score. Now the hard part is figuring out how to get Excel to manipulate the raw data into such a formula.

If I understand correctly you can put the scores for each property into cells and then use the average function on cells. In my screenshot look for row 3 - I used your example figures.

Since a picture says a thousand words:

I have the spreadsheet saved if you want it (but I do not have an antivirus so you may wish to test it if you're on Windows)

Well my only qualm with that is I cannot change the layout of the spreadsheet. So the formula would somehow have to decipher when there is a "6" in the cell, that means 6 evaluators gave it a score of say 1 or 2, depending on the location of the value. Sorry I did not clarify this in my first post.

Originally Posted by spin360

Well my only qualm with that is I cannot change the layout of the spreadsheet. So the formula would somehow have to decipher when there is a "6" in the cell, that means 6 evaluators gave it a score of say 1 or 2, depending on the location of the value. Sorry I did not clarify this in my first post.

So to be clear, you want a list of scores instead of a list of evaluators? Then in the main grid how many evaluators gave it that score?

To compare with my screenshot "evaluator " would become "score" and the grid would be "0-8" depending on how many evaluators gave that score?

Edit: Where the sum of b3 to f3 = 8 of course

edit2: yet the average will always be 1.6?

Originally Posted by e^(i*pi)

So to be clear, you want a list of scores instead of a list of evaluators? Then in the main grid how many evaluators gave it that score?

To compare with my screenshot "evaluator " would become "score" and the grid would be "0-8" depending on how many evaluators gave that score?

Edit: Where the sum of b3 to f3 = 8 of course

edit2: yet the average will always be 1.6?

I think so? In my case the score would only go to 5 instead of 8. So in the row below the "score", the value that goes there would be the sum of evaluators that gave the area that particular score. Then some formula to average those scores. So...

A B C D E F
Score 1 2 3 4 5
QTY 2 6 0 0 0
Average score for the area = 1.75

I hope that was clear. I cannot upload a screen shot of it anywhere so that is the best I can do.

edit: so apparently vBulletin doesn't like extra spaces...