SOLVED

Excel - Max Limit with Conditions

Contributor

I have a spreadsheet with several questions on it that can be rated into several categories.  For example:  

 InadequateBelow AverageAverageAbove AverageSuperior
Question 1x    
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?

4 Replies

@Kim-Kay 

See the attached demo workbook.

S1815.png

Can you do that with the actual values instead of the "x" placeholders? Sorry, my example should have reflected that.
Or, probably even better... if those cell are not blank?
best response confirmed by Kim-Kay (Contributor)
Solution

@Kim-Kay 

Use 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.