IFS returning calculations
Each branch can work out its own amount.
Task:
Handling fees: free for orders of 500 or more, 5% of the order from 100 up to 500, and a flat 9.99 below 100. In C2:C7 give each order's handling fee.
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 | Order | Value | Fee |
| 2 | H-1 | 620 | |
| 3 | H-2 | 240 | |
| 4 | H-3 | 99.5 | |
| 5 | H-4 | 500 | |
| 6 | H-5 | 100 | |
| 7 | H-6 | 45 |
What this exercise teachesMay contain the answer
Mixing fixed amounts and percentages is where nested IFs get hard to read; IFS keeps each band on one line of the formula. Watch the boundaries: an order of exactly 100 pays 5.00, and one of exactly 500 pays nothing.