Problem with a nested If Statement

[wright_s]

I am creating a simple spreadsheet to assess some student work. I am using a nested if statement to evaluate the totals in 3 cells and display a grade.

The problem is that it does not the evaluate the final statement and I cannot figure out why. The first two display with no problems. I have checked that the cells referred to in the formula display the correct totals. Can anyone suggest anything I have missed?

=IF(AND(C50<10,D50<=30,E50<=51),"Working towards a Pass",IF(AND(C50>=10,D50<30,E50>=0),"PASS",IF(AND(C50=10,D50>=30,E50>=0),"MERIT",IF(AND(C50=10,D50=30,E50=51),"DISTINCTION"))))

 

[energman58]

Your formula is fine as far as it goes but I suspect you have too many conditions for your If statement for the formula you have written

For example if say C>10 but D is less than 30 then your IFs all evaluate FALSE and the formula will return "FALSE" (as the final IF doesnt have a value for FALSE)

It looks like you have at least 3 conditions for each of the 3 marks which is 27 possible combinations of conditions (3 x 3 x3) so in theory you need 27 nested IF statements to deal with all the combinations of possibilities but - aside from going mad if you try to debug this - Excel limits you to a maximum of 7 nested IFs.

So if this is the case you need to develop a composite variable to combine the student marks into fewer categories so you can split the sheep and goats but you would need to explain what you are seeking before I could suggest something

 

[wright_s]

Thanks for the quick answer- I hadn't spotted the error in the final IF statement. After some experimentation it now works perfectly. Thanks!

IF(AND(C50<10,d50<=30,e50<=51),"Working towards a Pass",IF(AND(c50>=10,d50<30),"PASS",IF(AND(c50=10,d50=30,e50<=0),"MERIT",IF(AND(c50=10,d50=30,e50=51),"DISTINCTION",""))))