Lesson#7: How to make nested IF in Excel?

Lesson#7: How to make nested IF in Excel?

In this post we will discuss nested IF making. I have to show Grade on the mark sheet so I will check multiple conditions by IF.

The structure of nested IF will be like this =IF(E5<85,IF(E5<70,IF(E5<55,IF(E5<40, IF(E5<25, “F”, “E”), “D”), “C”), “B”),”A”) which is shown in above picture.
1st layer: If the mark is Less the 85 then it will check the condition else it will write “A”
2nd layer: If the mark is Less the 70 then it will check the condition else it will write “B”
3rd layer: If the mark is Less the 55 then it will check the condition else it will write “C”
4th layer: If the mark is Less the 40 then it will check the condition else it will write “D”
5th layer: If the mark is Less the 25 then it will write “F” else it will write “E”

In that way, the logic is created here to show the grade. Now I will show one more example of nested IF by showing the status of the student whether he is “PASS” or “FAIL”. Here I made the rule if someone passes in every subject then he will “PASS” else he will “FAIL”.

Here the structure becomes like  =IF(E7=”F”,”FAIL”,IF(F7=”F”,”FAIL”,IF(G7=”F”,”FAIL”,IF(H7=”F”,”FAIL”,IF(I7=”F”,”FAIL”,IF(J7=”F”,”FAIL”,”PASS”))))))
In multiple layers, IF function is checking whether the student has got an “F” in any subject or not., if TRUE he is “FAIL” if false in every subject then “PASS”.

I have shown precedents for the formula here. In my next posts, we will discuss more multiple-layer IF.

See also  Lesson#59: If A=1, Z=26 then calculate the value of your name

Related video tutorials:

Hi! I am Puspendu. I am the founder and author of Excelabcd. I am little creative person, blogger and Excel-maniac guy. I hope you enjoy my blog.

Leave a Reply

Your email address will not be published. Required fields are marked *

*