Home » Excel, VBA & Power BI » Excel Resources » Excel Nested If Function

Excel Nested If Function

Jeevan A Y
Article byJeevan A Y
Dheeraj Vaidya, CFA, FRM
Reviewed byDheeraj Vaidya, CFA, FRM

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.

NESTED IF Example 1

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.

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


    NESTED IF Example 1-1

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


    NESTED IF Example 1-2

  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.


    NESTED IF Example 1-3

  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.


    NESTED IF Example 1-4

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


    NESTED IF Example 1-5

  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.


    NESTED IF Example 1-6

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


    NESTED IF Example 1-7

  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.


    NESTED IF Example 1-8

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


    NESTED IF Example 1-9

  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.


    NESTED IF Example 10

    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.

Example #2

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

NESTED IF Example 2

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.
NESTED IF Example 2-1
  • Step 2: Apply second IF, if the first test is FALSE.
NESTED IF Example 2-2
  • Step 3: If the above IF conditions are FALSE then test the third condition.
NESTED IF Example 2-3
  • Step 4: If all the above conditions are FALSE then the result is 0%.
Example 2-4
  • Step 5: Copy down the formula to the remaining cells, we will have results.
Example 2-5

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.

Excel Nested IF Function Example 3

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.
Excel Nested IF Function Example 3-1
  • Step 2: Since we need to test two conditions to arrive the result lets open AND function inside the IF condition.
Excel Nested IF Function Example 3-2
  • 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.
Excel Nested IF Function Example 3-3
  • Step 4: If the supplied conditions are TRUE bonus amount is 50000.
Excel Nested IF Function Example 3-4
  • Step 5: Like this apply tests for remaining conditions. I have already applied the formula to arrive at the results.
Excel Nested IF Function Example 3-5

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.

Recommended Articles

This has been a guide to Nested IF Function in Excel. Here we discuss how to use Nested IF Function in Excel along with practical examples and downloadable excel template. You may learn more about excel from the following articles –

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Cookies help us provide, protect and improve our products and services. By using our website, you agree to our use of cookies (Privacy Policy)