SOLVED

# Excel - Max Limit with Conditions

Copper Contributor

# Excel - Max Limit with Conditions

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:

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

# Re: Excel - Max Limit with Conditions

See the attached demo workbook.

# Re: Excel - Max Limit with Conditions

Can you do that with the actual values instead of the "x" placeholders? Sorry, my example should have reflected that.

# Re: Excel - Max Limit with Conditions

Or, probably even better... if those cell are not blank?
best response confirmed by Kim-Kay (Copper Contributor)
Solution

# Re: Excel - Max Limit with Conditions

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.

1 best response

Accepted Solutions
best response confirmed by Kim-Kay (Copper Contributor)
Solution

# Re: Excel - Max Limit with Conditions

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.