SWITCH(TRUE, …) for ranges
Switch on TRUE and each "match" becomes a condition.
Task:
Stock cover is labelled Critical below 3 days, Low below 7, OK below 30, and Overstock from 30. In C2:C7 give each item's label using SWITCH with TRUE as the value.
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 SWITCH
0/4The same formula in the other shapes it takes at work.
- SWITCH Function
Use SWITCH function for value matching.
CoreAdvanced - SWITCH for text codes, with a default
Turn a one-letter code into a word, and say so when the code is unknown.
Intermediate - SWITCH returning a number inside a calculation
Look the tier's multiplier up and apply it in the same formula.
Advanced - SWITCH(TRUE, …) for ranges
Switch on TRUE and each "match" becomes a condition.
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 | Item | Days of cover | Status |
| 2 | Filters | 2 | |
| 3 | Belts | 6.5 | |
| 4 | Bearings | 14 | |
| 5 | Gaskets | 30 | |
| 6 | Valves | 3 | |
| 7 | Seals | 45 |
What this exercise teachesMay contain the answer
Plain SWITCH can only test equality, which is useless for ranges. Switching on TRUE turns each match into a condition, so SWITCH behaves like IFS with a built-in default. Because the thresholds are "less than", the order runs from smallest to largest — the reverse of the >= bands elsewhere.