Nested If Function

We use the Nested IF function if we have multiple criteria to find and decide the final output.

The Nested if the function uses the If function inside another if function when multiple conditions are to be fulfilled. In Simple language, it can be defined as the combination of multiple if functions in one formula.

Syntax

=IF( condition1, Value if true1 ,IF(Condition2, Value if true2, Value if false2)). 

Below is an example of the Nested if function.

Let’s take a dataset of students with their marks and we have to assign the grades based on the marks.

Formula used is : =IF(B2>=90,”A”,IF(B2>=80,”B”,IF(B2>=70,”C”,IF(B2>=60,”D”,”F”))))

Here, Multiple conditions are being checked.

Order of Nested if Function

In the above example, there were four logic tests. When the difficulty level of the logic test is equal then it does not matter which condition should be checked first.

But when the logic test has a different difficulty level, then the order of the condition matters. 

Let’s understand the concept of Logical test order of difficulty from the example below. 

Harder test first 

The formula used is =If(B2>=60,”1st division”, if(b2>=30,”2nd division”, if(B2>=10″3rd division”, “Fail”)))

The marks in B2 are greater than 60 so the result is “1st division” which passes the first logic test.

Easier test first

The formula used here is =IF(B2>=30,”2nd division”,IF(B2>=360,”1st division”,IF(B2>=10,”3rd division”,”fail”)))

Note: This is giving the incorrect result as B2 is 87 so it should give 1st division as the result.

The IF function is one of Excel’s most used, if not the most used functions. It helps perform a single or a series of logical comparisons between entities. It works using the same principles as IF-ELSE conditional statements, used in most programming languages. Values satisfying the given condition, result in a positive output, while values not satisfying the given condition, result in a negative output. Condition and Return values both can be decided by the user.