Nested IF returning numbers
Bands that return a price rather than a grade.
Task:
Delivery is free from 100, 4.99 from 50, and 7.99 below that. Put the delivery charge for each basket in C2:C7.
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 | Basket | Value | Delivery |
| 2 | B-01 | 125 | |
| 3 | B-02 | 49.99 | |
| 4 | B-03 | 50 | |
| 5 | B-04 | 100 | |
| 6 | B-05 | 12.5 | |
| 7 | B-06 | 87 |
What this exercise teachesMay contain the answer
Testing from the top band down means each IF only has to check one boundary — by the time the second IF runs, the basket is already known to be under 100. Returning numbers rather than words means the delivery column can go straight into the order total.