Level 2Level 2 ProjectsProject, 8 min
A commission calculator
The task
Five reps, one tiered commission plan. Build the calculator finance runs every month: attainment, the rate from the tier table, the payout, and who earned the most.
- Attainment for every repIn D2, divide Sales by Quota for all five reps at once:
=ARRAYFORMULA(C2:C6/B2:B6). - Look up each rateIn E2, find each rep's rate in the tier table (H:I). Attainment rarely matches a tier exactly, so ask XLOOKUP for the exact match or the next smaller value: match mode
-1.=ARRAYFORMULA(XLOOKUP(D2:D6, H2:H5, I2:I5, , -1)) - Work out the commissionIn F2, multiply each rep's Sales by their Rate, for all five rows.
- Total payoutIn F7, add up the commission column.
- Name the top earnerIn B9, return the rep with the biggest commission.
The data
The starting sheet. Cells outlined in green are where your formulas go.
| A | B | C | D | E | F | G | H | I | |
|---|---|---|---|---|---|---|---|---|---|
| 1 | Rep | Quota | Sales | Attainment | Rate | Commission | Tier from | Rate | |
| 2 | Maya | $60,000 | $74,400 | 0% | 0% | ||||
| 3 | Theo | $55,000 | $56,100 | 80% | 5% | ||||
| 4 | Priya | $50,000 | $41,500 | 100% | 8% | ||||
| 5 | Jonah | $65,000 | $48,750 | 120% | 10% | ||||
| 6 | Lena | $45,000 | $54,900 | ||||||
| 7 | Total | ||||||||
| 8 | |||||||||
| 9 | Top earner |