IF function and nested if furmula in excel

by

How to use IF FUNCTION AND NESTED IF ..?

In excel we can do logical test with if function, IF function will check the given condition, based on the output of the condition it can give two different results. If the given condition is YES, it will give one output and if it is NO then it will give another output. In this post we will learn how to use if function and also nested if formula.

IF FUNCTION

=IF(LOGICAL TEST,                     OUTPUT IF TEST RESULT IS YES,                     OUTPUT IF TEST RESULT IS NO)
After if and open bracket we should put logical test.
Eg: =IF(A8>10
This will check the cell value is greater than 10
After logical check we should put a comma and define the output if the logical test result is yes.
Eg: =IF(A8>10, “YES”
if the value of cell A8 is more than 10 the output will be YES in formula cell.
This output can be a String, formula or some other cell reference.
The output If the logical test result is no should be defined separated by a comma.
Eg: =IF(A8>10, “YES”,”NO”)
If the value of cell A8 is less than 10 the output will be NO in formula cell.
This output can be a String, formula or some other cell reference.

We can discuss if function in detail with one example:

In this example the passing marks of student is 40 or above, the marks obtained for the subject is given and this is to be checked and result pass of fail is to be updated.

=IF(D5>=40,”Pass”,”Fail”)
This formula will check the marks and give the result Pass or Fail depending upon the marks obtained.

IF FUNCTION

This is a simple if formula, but with if function we can do multiple logical test and get the results. This is called as nested if formula, Excel will allow you to nest up to 64 different IF functions in one formula.
In the same given example, if we want to check the different grades for marks obtained, we will have to apply more than one logical test.
The rule for grade for marks obtained is as below.
Marks
Result
70 and above
Distinction
60 and above but below 70
First class
50 and above but below 60
Second Class
40 and above but below 50
Pass
Less than 40
Fail
Depending on the marks obtained the formula will identify the grade and give the output.
Formula for the same and the results are in the below table.

NESTED IF FORMULA

=IF(D5>=70,”Distinction”,IF(D5>=60,”First class”,IF(D5>=50,”Second Class”,IF(D5>=40,”Pass”,”Fail”))))

In the nested if formula, the logical test will be done in following manner:

=IF(LOGICAL TEST1,OUTPUT IF TRUE, IF(LOGICALTEST2,OUTPUT IF TRUE, IF(LOGICALTEST3,OUTPUT IF TRUE,OUTPUT IF FALSE)))
The nested if formula can automate lot of manual work and save time. Please comment if you find any difficulty in using the nested if formula.