Hi:
I need to calculate a scoring sheet, to rate the performance of architects. The scoring sheet has five categories:
Category 1 = 3 statements
Category 2 = 11 statements
Category 3 = 3 statements
Category 4 = 4 statements
Category 5 = 4 statements
After rating each statement with a 1-5 rating, each category is summed (total points and percentage of points received) and then a percentage is given at the end to see where the architect rated.
I initially started by creating a formula for each category, to get a percentage total for that category:
First I totaled the points in each category, then I calculated the percentage based on those points. That gave me the percentage total for points scored for that category.
Category 1
to sum total score of 3 statements
=SUM(H2:H5)
to get percentage of three statements
=SUM(H6/15)
The final results were:
Category 1 = 11 out of 15 (73%)
Category 2 = 24 out of 55 (44%)
Category 3 = 15 out of 15 (100%)
Category 4 = 17 out of 20 (85%)
Category 5 = 16 out of 20 (80%)
Total = 76% (using the AVERAGE function)
How do I calculate the total if the number of categories changes? There will be times when only 3 or 4 categories will be rated. If I use the same formula at the end of the spreadsheet to get the overall percentage scored, the total percentage will not be reflective of the actual score using the AVERAGE function. Let’s say I don’t need to score category 2:
Category 1 = 11 (73%)
Category 2 = 0 (0%)
Category 3 = 15 (100%)
Category 4 = 17 (85%)
Category 5 = 16 (80%)
Total = 68% (using the AVERAGE function for five categories)
How do I tell the AVERAGE function that category 2 wasn’t used and shouldn’t be used to calculate the average? Using a revised AVERAGE function for 4 categories, the percentage goes up to 84.5%, which is the correct answer.
Joe
Bookmarks