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.
Related video tutorials:
Leave a Reply