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!