Nested IF practice exercises
A nested IF puts one IF inside another to handle more than two outcomes, like turning scores into letter grades. The trick is ordering the tests so the first one that's true gives the right answer.
Syntax
IF(test1, value1, IF(test2, value2, value_otherwise))
test1- Checked first. Put the strictest condition (the highest threshold) here.
IF(test2, ...)- Only checked when test1 is FALSE.
value_otherwise- The result when no test is TRUE.
The practice data
Test scores for 8 students, and a grade table (D1:E6) sorted from the lowest minimum score up. Every exercise below uses this sheet, and your answer goes in H2.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Student | Score | Min score | Grade | |
| 2 | Ana | 92 | 0 | F | |
| 3 | Ben | 67 | 60 | D | |
| 4 | Cara | 85 | 70 | C | |
| 5 | Dev | 58 | 80 | B | |
| 6 | Eli | 74 | 90 | A | |
| 7 | Fay | 100 | |||
| 8 | Gus | 81 | |||
| 9 | Hal | 69 |
Worked example
=IF(B2>=90,"A",IF(B2>=80,"B","C"))
Ana scored 92, so the first test is TRUE and the result is "A". Result:
| A |
Your turn: 3 exercises
Type a formula and press Enter. It is checked by a real formula engine, so any correct approach counts.
1 Label each score Distinction if 85 or more, Pass if 60 or more, otherwise Fail. Copied down to H9.
2 Give each score a letter grade: A 90+, B 80+, C 70+, D 60+, otherwise F. Copied down to H9.
3 Label each score Perfect if exactly 100, Excellent if 90 or more, otherwise -. Copied down to H9.
Practice Nested IF in the full lesson, free
Lesson 4 has more exercises in a real spreadsheet grid, with hints and progress tracking. No card needed.
Open lesson 4Common Nested IF mistakes
- Testing the lowest threshold first: =IF(B2>=60,"D",IF(B2>=90,"A",...)) labels everyone 60+ as "D".
- Miscounting the closing brackets at the end. Each IF needs its own ).
- Nesting 5+ levels when a lookup table with VLOOKUP approximate match or IFS would be clearer.
FAQ
How many IFs can I nest?
Google Sheets and Excel allow many (Excel allows 64), but beyond 3 or 4 a lookup table or IFS is easier to read and maintain.
Nested IF or IFS?
IFS lists each condition and result as a pair without nesting: =IFS(B2>=90,"A",B2>=80,"B",TRUE,"C").