How to Use Nested IF Else in Excel Formula (4 Suitable Ways)

Excel is the most widely used tool when it comes to dealing with huge datasets. We can perform myriads of tasks of multiple dimensions in Excel. We need to use nested IF else in Excel formula for various reasons. I am going to show you 4 suitable examples of using nested IF else in Excel.


Download Practice Workbook

Download this workbook while going through this article.


4 Suitable Examples of Applying Nested IF Else in Excel Formula

In this section, we will elucidate four effective examples of applying nested IF else in an Excel formula. Without further ado, let’s move on to our examples.


1. Apply Nested IF Else Condition to Prepare a Grade Sheet

The most frequent use of nested IF is perhaps the preparation of a grade sheet. We will see how to do that. This is the dataset for this example, where we have the marks for some students and our task is to assign the grades.

nested if else in excel formula

The grades are given below.

Marks Grades
80 or Above A
70-79 B
60-69 C
Below 60 F

Steps:

  • Go to D5 and write down the formula
=IF(C5<60,"F",IF(C5<70,"C",IF(C5<80,"B","A")))

Explanation:

  • Excel tests the logical tests one by one. First, it tests C5<60.
  • C5<60 is FALSE. That’s why it checks the next logical test. That is C5<70.
  • C5<70 is FALSE. So the next logical test is checked. That is C5<80.
  • C5<80 is also FALSE. Since all the logical tests are FALSE, The output is “A
  • Now press ENTER. Excel will return the output.

nested if else in excel formula


2. Use a Combination of Nested IF and OR Functions

Now I will show how to use nested IF and OR functions together. This is the dataset for this example.

nested if else in excel formula

I have listed some footballers and am going to give remarks based on their performances. The table demonstrates the remarks with the criteria.

Criteria Remark
No. of Goal or Assist is above 30 Extraordinary
Either Goal or Assist is above 18 but both are below 30 Moderate
Goal and Assist both are below 18 Poor

Steps:

  • Go to E5 and write down the formula
=IF(OR(C5>30,D5>30),"Extraordinary",IF(OR(C5>=18,D5>=18),"Moderate",IF(OR(C5>=0,D5>=0),"Poor")))

Explanation:

  • The first logical test is OR(C5>30,D5>30). Since neither the goal (13) nor the assist (10) for Higuain is greater than 30, the test is FALSE.
  • Similarly, the next test OR(C5>=18,D5>=18) is FALSE.
  • The next test OR(C5>=0,D5>=0) is TRUE for Higuain. So the output is “Poor
  • Now press ENTER. Excel will return the output.

nested if else in excel formula

  • Now use Fill Handle to AutoFill up to E11.


3. Apply Nested IF and AND Formula in Excel

In this section, I will show you how to use nested IF and AND functions together. This is the dataset for this example.

nested if else in excel formula

I have listed some footballers and am going to give remarks based on their performances. The table demonstrates the remarks with the criteria.

Criteria Remark
No of Goal & Assist are above 20 Extraordinary
No of Goal & Assist are above 10 but below 20 Moderate
Goal and Assist both are below 10 Poor

Please note that to keep the example simple, we have arranged the dataset in a way that the conditions are met. For instance, no conditions are met when a number of goals is 5 but a number of assists is 15. So we omitted that.

Steps:

  • Go to E5 and write down the formula
=IF(AND(C5>20,D5>20),"Extraordinary",IF(AND(C5>=10,D5>=10),"Moderate",IF(AND(C5>=0,D5>=0),"Poor")))

Explanation:

  • The first logical test is AND(C5>20,D5>20). Since both the goal (25) and the assist (27) for Ronaldo is greater than 20, the test is TRUE. So the output is “Extraordinary
  • Now press ENTER. Excel will show the output.

nested if else in excel formula

  • Now use the Fill Handle to AutoFill up to E11.


4. Perform a Combination of Nested IF and SUM Functions in Excel

You can also use a combination of nested IF and SUM functions for various purposes. In this section, I will give an example of that.

I will use the same dataset. But the criteria will change a bit.

Criteria Remark
The Sum of Goal and Assist is above 40 Selected For A Team
Sum of Goal and Assist >= 20 but below 40 Selected For B Team
The Sum of Goal and Assist is below 20 Selected For C Team

Steps:

  • Go to E5. Write down the following formula
=IF(SUM(C5:D5)>40,"Selected For A Team",IF(SUM(C5:D5)>=20,"Selected For B Team","Selected For C Team"))

nested if else in excel formula

Explanation:

  • The first logical test is SUM(C5:D5)> 40.
  • SUM(C5:D5)> 40 is FALSE. That’s why it checks the next logical test. That is SUM(C5:D5)>=20.
  • SUM(C5:D5)>=20 is TRUE. So the output is “Selected For B Team
  • Press ENTER to see the output.

  • Now use the Fill Handle to AutoFill up to E11.

nested if else in excel formula


Things to Remember

  • You can use the IFS function as an alternative to nested IF else in Excel
  • Excel checks the logical tests serially. When it finds a logical test TRUE, it stops testing other tests and returns output. But if all the tests are FALSE, then Excel will show the output that is used in the “value if false” argument.

Conclusion

In this article, I have demonstrated 4 suitable examples of using nested IF else in excel formula. I hope it helps everyone. If you have any kind of suggestions, ideas, or feedback,  please feel free to comment down below.

Akib

Akib

Hi, this is MD Akib Bin Rashid. I completed my BSc in Engineering back in 2019. After that, I did an MBA. Then, I joined as an Excel and VBA Content Developer at SOFTEKO Digital. Being passionate about data analytics and finance, I will try to assist you with Excel

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo