The IF function on Excel is very handy for comparisons of logic and returning set results depending on whether the condition is met or not.
However, when you need to evaluate multiple conditions from a single formula, it is at this point that you would require a nested IF function.
Another nice way of working with IF functions in Excel is nesting them to handle a more complex logic statement all to be performed within one cell, testing several criteria, and returning a different answer for each criterion.
We’ll discuss examples and practical uses of nested IF functions in Excel to ease your workflows.
Basics of the IF Function
The syntax for the IF function is very straightforward:
=IF(logical_test, value_if_true, value_if_false)
It tests the logical test, and if this test is true, it returns the value “if true”. If not, then it returns the value “if false”. But when you have a selection of several conditions, you would put additional IF functions within the value if-false branch of another IF function to consider different alternatives.
Why Use Nested IF Functions?
Nested IF functions are handy when it comes to weighing a number of criteria. For example:
-
Calculate and distribute grades according to scores.
-
Offer various discounts that change based on the quantity of purchases.
-
Letting the investigator understand how to categorize income levels.
With nested IF, you can have many conditions on different levels, so your calculations are easier without requiring lots of columns or rows in separate cells.
Nested IF examples
Example 1:
Grading Scale with Nested IF
Assume that you have Column A with student grades, and you want to fill in a column with grades A, B, C, D, or F according to the following scale:
A: 90-100
B: 80-89
C: 70-79
D: 60-69
F: Below 60
The following nested IF formula lets you do this exercise:
=IF(A2 >= 90, “A”, IF(A2 >= 80, “B”, IF(A2 >= 70, “C”, IF(A2 >= 60, “D”, “F”))))
Explanation:
So the first IF in the formula checks if the score in cell A2 is 90 or higher. If this is true, it returns “A. If this returns false, it then moves to the next IF statement to see if the score is 80 or higher. If true, it returns “B. This pattern continues through each IF checking a different range of scores until it finally assigns a grade.
Example 2:
Tiered Discounts Based on Purchase Volume
Assume, a retailer discounts goods according to a purchase volume. The rules are:
20% discount for orders more than 100 units.
15% discount for orders more than 50 units.
10% discount for orders more than 20 units.
5% discount for orders more than 10 units.
No discount for any order less than 10 units.
To depict such a scenario, a nested IF function can be employed as described below:
=IF(B2> 100,0.2,IF(B2 >50,0.15, IF(B2> 20,0.1,IF(B2 > 10,0.05, 0))))
Explanation:
The formula looks into if the quantity ordered is over 100. In such case, apply 20 percent discount. Otherwise, verify whether ordered is more than 50 then 15% would apply and all such scenarios.
Example 3:
Income Category Classification
Assume you group individuals by their income ranges:
High income: More than Rs.100,000
Upper-middle income: Rs.75,000 – Rs.100,000
Middle income: Rs.50,000 – Rs.75,000
Low income: Less than Rs.50,000
Using a nested IF function, it will appear as follows:
=IF(C2 > 100000, “High income”, IF(C2 > 75000, “Upper-middle income”, IF(C2 > 50000, “Middle income”, “Low income”)))
Explanation:
This formula has the effect of categorizing the income levels; it does this by comparing the amount in cell C2 with the above set threshold levels by the highest first.
Using Nested IF Functions
-
Limit Nesting: Even though Excel allows up to 64 levels of nested IF functions, over-nesting can make formulas illegible and unmaintainable. AVOID EXCESSIVE NESTING.
-
Use Named Ranges: Use named ranges instead of direct cell references for further clarity on what each condition is intended to refer to.
-
Explore Using Alternative Functions: One alternative way to use nested IFs sometimes instead is using functions such as IFS, CHOOSE, VLOOKUP, or INDEX and MATCH to simplify formulas.
Nested IFs is an extremely powerful manner of testing for multiple conditions and returning different results within one formula. You can handle complex cases right in the worksheet without taking extra columns using a series of multiple IF statements.