Excel track · Exercise 25 of 86 Paid
IFS: more than two outcomes
Read a score into one of several bands with a single ordered function.
Advanced
Four-name score sheet with bands to decide
Read-only data · screen 1 of 2 · answer in B6
In B6, band Ben's score: 90 and above is A, 80 and above B, 70 and above C, otherwise D.
| A | B | |
|---|---|---|
| 1 | Name | Score |
| 2 | Ada | 92 |
| 3 | Ben | 78 |
| 4 | Cleo | 85 |
| 5 | Dev | 64 |
| 6 | Bands |
In B6, label Cleo: 90 and above is Merit, 80 and above Pass, anything lower is Resit.
| A | B | |
|---|---|---|
| 1 | Name | Score |
| 2 | Ada | 92 |
| 3 | Ben | 78 |
| 4 | Cleo | 85 |
| 5 | Dev | 64 |
| 6 | Labels |
Reference answer
What it does
IFS takes pairs of test and result and returns the result of the first test that comes out TRUE. Tests and results alternate, so three bands need three tests and three labels. The order of the pairs decides the answer, because the first match wins and nothing below it is read.
Ben's 78 fails 90 and fails 80, then lands on the 70 test, so B6 shows C. The second screen labels Cleo's 85 as Pass, since Merit needs 90. Raise the pass bar to 86 and her own score falls through to the fallback.
Close the list with TRUE and a fallback, or a score under every test returns #N/A. Older versions of Excel have no IFS, so the same logic turns up as nested IFs, and those are worth being able to read.
Syntax
=IFS(test1, result1, test2, result2, ..., TRUE, fallback)Worked example
On Four-name score sheet with bands to decide, =IFS(B3>=90,"A",B3>=80,"B",B3>=70,"C",TRUE,"D") returns C.
=IFS(B3>=90,"A",B3>=80,"B",B3>=70,"C",TRUE,"D")Common errors
| Error | What it means and how to fix it |
|---|---|
#N/A | No test was TRUE and there was no TRUE fallback pair, so a score under the last band has nothing to return. |
#VALUE! | The pairs are uneven and a test has no result of its own. |
#NAME? | The version of Excel in use has no IFS, or the name is mistyped. |
At work
Banding scores or service levels into labels without a lookup table.