Grades with IFS and IFERROR: practice exercises
Nested IF, IFS, IFERROR. 4 exercises on real-looking data, each marked the moment you press Check. The first exercise is below to try now; the full lesson is part of Pro.
In C2, use one IF inside another to show Merit for 60 or more, Pass for 40–59 and Fail below 40.
| A | B | C | |
|---|---|---|---|
| 1 | Employee | Score | Level |
| 2 | Grace | 72 | |
| 3 | Tom | 55 | |
| 4 | Priya | 38 | |
| 5 | Marek | 64 | |
| 6 | Nia | 41 | |
| 7 | Ollie | 29 | |
| 8 | Lucy | 80 | |
| 9 | Hamza | 47 | |
| 10 | Rachel | 59 | |
| 11 | Sunil | 33 |
How it works
To give more than two outcomes, put an IF inside another IF, or use IFS, which checks each test in order and stops at the first one that's true. Check the highest boundary first.
Use TRUE as the last test in IFS to catch everything else. IFERROR(formula, alternative) shows something tidier than #DIV/0! or #VALUE! when a formula fails.
=IF(B2>=70,"High",IF(B2>=50,"Mid","Low")) =IFS(B2>=70,"High",B2>=50,"Mid",TRUE,"Low") =IFERROR(B2/C2,"No mark")
More than two outcomes
IF gives one of two results. For more, such as High, Mid and Low, you can put a second IF inside the first, or use IFS, which takes a list of tests.
Either way, check the highest boundary first. Excel stops at the first test that's true.
Worked example: an IF inside an IF
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Name | Score | Grade | Grade (IFS) |
| 2 | Amira | 72 | ||
| 3 | Ben | 55 | ||
| 4 | Cara | 31 | ||
| 5 | Dev | 60 |
=IF(B2>=70,"High",in . Check the highest boundary first: if the score is at least 70, it's High.=IF(B2>=70,"High",IF(B2>=50,"Mid","Low"))in . If not, the false result is another IF: at least 50 is Mid, and anything else is Low. Amira's 72 is High.=IF(B2>=70,"High",IF(B2>=50,"Mid","Low"))in . Ben's 55 isn't 70 or more, so the second IF checks it: 50 or more, so Mid.
Worked example: IFS
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Name | Score | Grade | Grade (IFS) |
| 2 | Amira | 72 | ||
| 3 | Ben | 55 | ||
| 4 | Cara | 31 | ||
| 5 | Dev | 60 |
=IFS(B2>=70,"High",B2>=50,"Mid",TRUE,"Low")in . IFS takes pairs of test and result, checks them in order and stops at the first true one. TRUE as the last test catches everything else. Same answers, fewer brackets.
Worked example: IFERROR
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Name | Mark | Out of | Percentage |
| 2 | Amira | 30 | 40 | |
| 3 | Ben | ABS | 40 |
=B2/C2in . Ben has ABS instead of a mark, so dividing gives #VALUE!.=IFERROR(B2/C2,"No mark")in .IFERROR(formula, what to show instead)gives the normal answer when it works, and No mark when it would be an error.
What you'll practise
- In C2, use one IF inside another to show Merit for 60 or more, Pass for 40–59 and Fail below 40.
- In C2, use IFS to give Distinction (70+), Merit (60+), Pass (40+) or Fail.
- People who missed the assessment have "ABS" instead of a mark, which makes the percentage show an error. In D2, divide the mark by the total, but show Absent if that gives an error.
- The lowest sales for each award are in H1:H3, so they can be changed next year. In C2, use IFS to give Gold, Silver, Bronze or None, using those cells.