SWITCH returning a number inside a calculation
Look the tier's multiplier up and apply it in the same formula.
Task:
Loyalty tiers get a discount: Gold pays 80% of list price, Silver 90%, and everyone else full price. In D2:D7 give each customer's price.
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 | D | |
|---|---|---|---|---|
| 1 | Customer | Tier | List price | Price |
| 2 | Ines | Gold | 250 | |
| 3 | Jorge | Silver | 250 | |
| 4 | Kai | 250 | ||
| 5 | Lea | Gold | 90 | |
| 6 | Mo | Bronze | 120 | |
| 7 | Nia | Silver | 40 |
What this exercise teachesMay contain the answer
Returning the multiplier, not the finished price, keeps the formula to one copy of C2 and makes the policy readable at a glance. The default of 1 is a real decision: Bronze, blank and any future tier pay full price until someone says otherwise.