Akib Bin Rashid
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.
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.
- Now use Fill Handle to AutoFill up to D10.
Read More: How to Use Nested IF Function in Excel (6 Ideal Examples)
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.
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.
- 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.
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.
- Now use the Fill Handle to AutoFill up to E11.
Read More: How to Create a Nested Formula with IF and AND Functions in Excel
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"))
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.
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.
