Excel Nested IF Function

Nested IF Function in Excel

In excel nested if function means we use another logical or conditional function with the if function to test more than one conditions, for example, if there are two conditions to be tested we can use the logical functions AND or OR function depending upon the situation, or we can use the other conditional functions even more ifs inside a single if.

Examples

The following examples are used to calculate the Nested IF Function in Excel:

You can download this Nested IF Function Excel Template here – Nested IF Function Excel Template

Example #1

Now take a look at the popular nested IF example. Based on the student’s score we need to arrive at their standards. Consider the below data for an example.

In order to arrive at the results we need to test below conditions and these conditions are nothing but our logical tests.

If the score is >=585 result should be “Dist”
If the score is >=500 result should be “First”
If the score is >=400 result should be “Second”
If the score is >=350 result should be “Pass”
If all the above conditions are FALSE then the result should be FAIL.

Ok, we have a totally of 5 conditions to test. The moment logical tests are more than on we need to use nested IF’s to test multiple criteria.

Step 1: Open IF condition and pass the first test i.e. test whether the score is >=585 or not.

Step 2: Now if the above logical test is TRUE then we need the result as “Dist”. So enter the result in double-quotes.

Step 3: Now the next argument is if the value or test is FALSE. If the test is false I have 4 more conditions to test, so open one more IF condition in excel in the next argument.

Step 4: Now test the second condition here. The second condition is to test whether the score is >= 500 or not. So pass the argument as >=500.

Step 5: If this test is true the result should be “First”. So enter the result in double-quotes.

Step 6: We have already entered two excel IF conditions, if these two tests are FALSE then we need to test the third condition, so open one more IF now and pass the next condition i.e. test whether the score is >=400 or not.

Step 7: Now if this test is TRUE the result should be “Second”.

Step 8: Now the total number IF conditions are 3. If all these IF conditions test is FALSE we need one more condition to test i.e. whether the score is >=300.

Step 9: If this condition is TRUE then the result is “Pass”.

Step 10: Now we came to the last argument. Totally we have entered 4 IF’s, so if all these conditions tests are FALSE then the final result is “FAIL”, so enter “FAIL” as the result.

This is how we can test multiple conditions by nesting many IF conditions inside the one IF condition.

The logic here is the first IF result will come if the logical test is TRUE if the logical test is FALSE then the second IF will be executed. Like this, until the formula finds the TRUE test result it will be executed. If none of the results is TRUE then the final FALSE result will be executed.

Popular Course in this category

All in One Excel VBA Bundle (35 Courses with Projects)4.9 (1,353 ratings) 35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion

View Course

Example #2

Now take look at the real-time corporate example of calculating sales commission. Consider the below data for the example.

In order to arrive at the commission %, we need to test the below conditions.

If the sales value is >=7 lakh, commission % is 10%.
If the sales value is >=5 lakh, commission % is 7%.
If the sales value is >=4 lakh, commission % is 5%.
If the sales value is < 4 lakh, the commission is 0%.

This is very similar to the previous example. Instead of arriving results, we need to arrive percentages as the result, let’s apply nested IF Function in excel.

Step 1: Apply IF and test the first condition.

Step 2: Apply second IF, if the first test is FALSE.

Step 3: If the above IF conditions are FALSE then test the third condition.

Step 4: If all the above conditions are FALSE then the result is 0%.

Step 5: Copy down the formula to the remaining cells, we will have results.

Example #3

Take an example of how to use other logical functions AND with IF condition to test multiple conditions.

Take the same data from the above example, but I have slightly changed the data, I have removed the Sales column.

Here we need to calculate a bonus for these employees based on the below conditions.

If the employee’s department is Marketing & Year of service it is >5 years then the bonus is 50000.
If the employee’s department is Sales & Year of service it is >5 years then the bonus is 45000.
For all the other employees if the service is > 5 years, the bonus is 25000.
If the year of service is <5 years, the bonus is zero.

This looks a bit completed, isn’t it?

In order to arrive at a single result, we need to test two conditions. When we need to test two conditions and if both the conditions should be true the AND logical condition will be used.

AND will return the result is TRUE if all the supplied conditions are TRUE. If any of the one condition is FALSE then the result will FALSE only.

Step 1: Open the IF condition first.

Step 2: Since we need to test two conditions to arrive the result lets open AND function inside the IF condition.

Step 3: Here we need to test the conditions. The first condition is whether the department is Marketing or not and the second condition is a year of service is >=5 years.

Step 4: If the supplied conditions are TRUE bonus amount is 50000.

Step 5: Like this apply tests for remaining conditions. I have already applied the formula to arrive at the results.

Things to Remember

AND will return the TRUE result if all the supplied conditions are TRUE. If anyone of the condition is FALSE then it will return FALSE as the result.
In order to arrive at the final result, you need to apply one more rather you can pass the result in the FALSE argument only.