Concept
1. The problem: more than two answers
IF only gives two paths — true or false. But grading needs 4 results:
| Marks | Grade |
|---|---|
| 90 or more | A |
| 75–89 | B |
| 50–74 | C |
| below 50 | F |
2. Nested IF
The idea: put another IF in the false part.
=IF(B2>=90, "A", IF(B2>=75, "B", IF(B2>=50, "C", "F")))
Excel reads it top to bottom: first check 90; if that fails, check 75; if that fails, check 50; if nothing matches, "F". It stops at the first condition that is true.
Order is everything. If you write it the other way round:
=IF(B2>=50, "C", IF(B2>=75, "B", ...)) ❌
Someone with 95 marks will also get "C", because 95 ≥ 50 was already true. Always put the strictest (highest) condition first, or the lowest first — but in one consistent direction.
3. Counting closing brackets
As many IFs, that many closing brackets at the end. 3 IFs = ))). Excel colours each bracket pair differently — watch them as you type.
4. IFS — the clean way (Excel 2019 / 365)
=IFS(B2>=90, "A", B2>=75, "B", B2>=50, "C", TRUE, "F")
Structure: condition, result, condition, result... in pairs. No nesting, no bracket headaches.
Why TRUE at the end? IFS has no "otherwise" (else) argument. If no condition matches, you get #N/A. TRUE is always true, so it acts as "everything else".
5. When to use which
| Situation | Choose |
|---|---|
| 2–3 results, simple logic | IF / small nested IF |
| 4+ levels, grading/slabs | IFS |
| File will be opened on old Excel (2016 or earlier) | Nested IF (IFS gives #NAME? there) |
| 7–8+ levels, or slabs change often | Lookup table (covered in the Lookup module) |
6. Real example: commission slab
| Sales | Commission |
|---|---|
| 1,00,000+ | 8% |
| 50,000–99,999 | 5% |
| 25,000–49,999 | 2% |
| below 25,000 | 0% |
=IFS(C2>=100000, C2*8%, C2>=50000, C2*5%, C2>=25000, C2*2%, TRUE, 0)
Common mistakes
Writing conditions in the wrong order (the most common). Forgetting TRUE in IFS, which gives #N/A in some cells. Leaving a gap at a boundary — e.g. writing >75 after >=90, so someone with exactly 75 falls into no slab.