IFNA as a default inside a calculation
A missing lookup becomes a sensible default, and the arithmetic carries on.
Task:
Discounts depend on the customer's tier, looked up from the table in F2:G4. Customers with no tier get no discount. In D2:D7 give each order's price after discount: list price × (1 − discount).
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 IFNA
0/3The same formula in the other shapes it takes at work.
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 | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Customer | Tier | List price | Net price | Tier | Discount | |
| 2 | Amber Ltd | Gold | 400 | Gold | 0.15 | ||
| 3 | Basalt Inc | Silver | 250 | Silver | 0.1 | ||
| 4 | Cobalt Co | 300 | Bronze | 0.05 | |||
| 5 | Dune LLP | Bronze | 120 | ||||
| 6 | Egret plc | Platinum | 500 | ||||
| 7 | Flint Ltd | Silver | 80 |
What this exercise teachesMay contain the answer
The fallback does not have to be the final answer; it can be an input to the next step. Defaulting the missing discount to 0 lets every row price correctly, including Egret's Platinum tier that nobody has set up yet — which is worth a note to whoever owns the table.