Commission rates with IFS
PremiumReps earn 10% commission on sales of 10,000 or more, 5% from 5,000 and nothing below that. In C2, multiply the sales in B2 by the rate IFS picks: B2*IFS(B2>=10000,0.1,B2>=5000,0.05,TRUE,0).
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Rep | Sales | Commission | |||
| 2 | Ann | 12000 | ||||
| 3 | Ben | 7400 | ||||
| 4 | Cy | 3900 | ||||
| 5 | Di | 10000 | ||||
| 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
IFS returns the value after the first test that is TRUE, so the bands go from the highest down. Ann's 12,000 passes B2>=10000 straight away, IFS returns 0.1 and the commission is 12,000 × 0.1 = 1,200. Filled down, Ben gets 370, Cy 0 and Di, at exactly 10,000, gets 1,000.
Common mistake: Putting the lowest band first. Every sale of 10,000 or more also passes >=5000 and stops there.
The answer
- C2
=B2*IFS(B2>=10000,0.1,B2>=5000,0.05,TRUE,0)
Let IFS pick the rate and multiply, =B2*IFS(B2>=10000,0.1,B2>=5000,0.05,TRUE,0). Test the highest band first.