Lesson 1.3 – Nested IF Statements
1. What Are Nested IF Statements?
A nested IF is when one IF function is placed inside another IF function to test multiple conditions in sequence.
They are useful when you need different results based on multiple ranges or categories.
A. Syntax
-
condition1 → Checked first.
-
If TRUE, returns result1.
-
If FALSE, moves to the next IF.
-
This continues until all conditions are tested.
B. Example – Grading System
| Marks (A1) | Formula Output |
|---|---|
| 85 | Excellent |
| 70 | Good |
| 50 | Average |
| <50 | Fail |
Formula:
C. Another Example – Sales Commission
If sales are:
-
≥ 1,00,000 → 20% commission
-
≥ 50,000 → 10% commission
-
Otherwise → No commission
Formula:
D. Tips for Using Nested IF
-
Keep nesting simple; too many nested IFs can be hard to read.
-
Consider using IFS function (Excel 2016+) for cleaner formulas:
-
Always handle error cases where no condition is met.
E. Limitations
-
Older Excel versions allowed only 7 nested IFs.
-
Complex logic is better handled with LOOKUP or SWITCH functions.
Practice Questions – Lesson 1.3
-
Write a nested IF formula to return:
-
“A” if score ≥ 90
-
“B” if score ≥ 75
-
“C” if score ≥ 50
-
“F” if less than 50
-
-
Modify the sales commission example to add a 15% commission for sales ≥ 75,000.
-
Why might you replace a nested IF with a LOOKUP function?
-
True or False: IFS function works in Excel 2010.