Problem with a nested If Statement

wright_s

New Member
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?

Code:
``=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"))))``

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
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

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

Code:
``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",""))))``

Replies
4
Views
147
Replies
3
Views
223
Replies
1
Views
179
Replies
4
Views
119
Replies
4
Views
169

1,203,610
Messages
6,056,296
Members
444,855
Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back