Confident Cells

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.

Your turn

In C2, use one IF inside another to show Merit for 60 or more, Pass for 40–59 and Fail below 40.

ABC
1EmployeeScoreLevel
2Grace72
3Tom55
4Priya38
5Marek64
6Nia41
7Ollie29
8Lucy80
9Hamza47
10Rachel59
11Sunil33

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

ABCD
1NameScoreGradeGrade (IFS)
2Amira72
3Ben55
4Cara31
5Dev60
  1. =IF(B2>=70,"High", in . Check the highest boundary first: if the score is at least 70, it's High.
  2. =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.
  3. =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

ABCD
1NameScoreGradeGrade (IFS)
2Amira72
3Ben55
4Cara31
5Dev60
  1. =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

ABCD
1NameMarkOut ofPercentage
2Amira3040
3BenABS40
  1. =B2/C2 in . Ben has ABS instead of a mark, so dividing gives #VALUE!.
  2. =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

  1. In C2, use one IF inside another to show Merit for 60 or more, Pass for 40–59 and Fail below 40.
  2. In C2, use IFS to give Distinction (70+), Merit (60+), Pass (40+) or Fail.
  3. 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.
  4. 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.