Oct 03 2022 01:45 PM
I have a spreadsheet with several questions on it that can be rated into several categories. For example:
Inadequate | Below Average | Average | Above Average | Superior | |
Question 1 | x | ||||
Question 2 | x | ||||
Question 3 | x | ||||
Question 4 | x | ||||
Question 5 | x |
Each question has a point rating of:
Inadequate Category = 10 points
Below Average Category = 15 points
Average = 20 points
Above Average = 25 points
Superior = 30 points
I need to get the total score of each section (the table above is considered one section), but my problem is that, no matter how many questions are answered as Average, Above Average, or Superior, if even one is answered as Inadequate or Below Average, then the highest possible score is limited to Average. What formula can I use that will calculate the total and apply the max possible of Average if one of the answers is Inadequate or Below Average? Since "Standard" has 5 questions at 20 points each, a Standard rating would = 100 points. But, if the total is less than the 100 points due to multiple inadequate or below average answers, I need that to show (I don't want it to increase the sum to 100 points if the total is less than that).
Anyone able to help with this one?
Oct 03 2022 02:08 PM
Oct 03 2022 02:25 PM
Oct 03 2022 02:25 PM
Oct 03 2022 03:08 PM
SolutionUse the formula
=SUMPRODUCT(IF(COUNTIF(B3:C7,"<>"),IF(B1:F1>D1,D1,B1:F1),B1:F1)*(B3:F7<>""))
It won't matter whether you enter the description or x or something else in the cells - all that matters is that they aren't empty.
Oct 03 2022 03:08 PM
SolutionUse the formula
=SUMPRODUCT(IF(COUNTIF(B3:C7,"<>"),IF(B1:F1>D1,D1,B1:F1),B1:F1)*(B3:F7<>""))
It won't matter whether you enter the description or x or something else in the cells - all that matters is that they aren't empty.