Nested IF: why the order of tests matters
Test the smallest band first and the bigger ones are never reached.
Task:
Volume discount: 5% over 500, 10% over 1,000 and 15% over 2,000. The draft formula tests over 500 first, so nobody ever gets more than 5%. In C2:C7 write the discount rate correctly.
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 IF
0/4The same formula in the other shapes it takes at work.
- Nested IF Functions
Create complex conditions using nested IF statements.
CoreAdvanced - Nested IF returning numbers
Bands that return a price rather than a grade.
Intermediate - Nested IF: why the order of tests matters
Test the smallest band first and the bigger ones are never reached.
Advanced - Nested IF with AND in one branch
One band needs two conditions; the next needs only one.
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 | Discount |
| 2 | O-11 | 450 | |
| 3 | O-12 | 650 | |
| 4 | O-13 | 1200 | |
| 5 | O-14 | 2500 | |
| 6 | O-15 | 1000 | |
| 7 | O-16 | 2000.01 |
What this exercise teachesMay contain the answer
Every order over 2,000 is also over 500, so a formula that asks about 500 first answers 5% and never looks further. With greater-than thresholds, go from the largest down; with less-than thresholds, from the smallest up. 1,000 exactly is not over 1,000, so it gets 5%.