Shipping price bands with IFS
PremiumShipping costs 45 up to 1 kg, 69 up to 5 kg and 110 for anything heavier. In C2, use IFS to find the price for the weight in B2.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Parcel | Weight (kg) | Price | |||
| 2 | P-118 | 4.6 | ||||
| 3 | ||||||
| 4 | ||||||
| 5 | ||||||
| 6 | ||||||
| 7 | ||||||
| 8 |
Premium lesson
Unlock IFS and SWITCH and every other lesson
This is a premium lesson. Premium lets you solve it and every other lesson, take the timed mock tests, and earn certificates you can share on LinkedIn.
Next with premium:Conditional formattingDrop-down lists and data validation
Need help?
Why this works
When the bands say "up to", go from the smallest band upwards: 4.6 is not <= 1, but it is <= 5, so IFS returns 69. The prices are numbers without quotes, so you can add them up or multiply them later.
Common mistake: Putting the prices in quotes. "69" is text, and a SUM of the Price column then gives 0.
The answer
- C2
=IFS(1B2<=1,245,3B2<=5,469,5TRUE,6110)
In plain words: 1If the weight in B2 is up to 1 kg, 2the price is 45. 3If it is up to 5 kg, 4it is 69. 5For anything heavier, 6it is 110.