IFS with a TRUE catch-all
Without a final TRUE, any value that fits no band comes back as #N/A.
Task:
Customer satisfaction scores are labelled Excellent from 90, Good from 75, Fair from 50, and Poor below that. In C2:C7 give each store's label with one IFS.
Interactive Spreadsheet
Hints
+5 XPno-hints bonusSolve it on your own to keep the bonus. Each hint gets one step closer to the formula.
Variations of IFS
0/4The same formula in the other shapes it takes at work.
- IFS Function
Use IFS function for multiple conditions without nesting.
CoreAdvanced - IFS with a TRUE catch-all
Without a final TRUE, any value that fits no band comes back as #N/A.
Intermediate - IFS returning calculations
Each branch can work out its own amount.
Advanced - IFS with a compound first test
The most specific case first, the general cases after.
Advanced
The data in this exercise
This is the grid you start with. Cell references in the task — B6, C2 — point at the row numbers and column letters below.
| A | B | C | |
|---|---|---|---|
| 1 | Store | Score | Label |
| 2 | Ashford | 92 | |
| 3 | Bicester | 75 | |
| 4 | Crewe | 48 | |
| 5 | Dover | 81 | |
| 6 | Ely | 50 | |
| 7 | Frome | 12 |
What this exercise teachesMay contain the answer
IFS has no "otherwise" argument; a value that passes no test returns #N/A. Making the last test the literal TRUE gives it one. Crewe and Frome would both be #N/A without it, which is exactly the kind of gap nobody notices until a report goes out.