Sales Commission Tiers
Three rates, one threshold table — get the order wrong and the wrong tier wins.
Task:
You're closing out the month in sales operations. Reps earn 5% commission on sales up to $10,000, 7% on sales from $10,000 up to $25,000, and 10% on sales of $25,000 or more. In column C work out each rep's commission rate, then in column D multiply it by their sales to get what they're owed.
Learning Objectives:
- Order IFS conditions from most restrictive to least, so a higher tier isn't shadowed by a lower one
- Use TRUE as an IFS catch-all for "otherwise"
- Keep a tiered rate visible as its own column rather than folding it into one formula
Solve without hints for +5 XP
Interactive Spreadsheet
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 | D | |
|---|---|---|---|---|
| 1 | Rep | Sales | Commission Rate | Commission Owed |
| 2 | Alicia Chen | 8200 | ||
| 3 | Marcus Webb | 15400 | ||
| 4 | Diego Fuentes | 24999 | ||
| 5 | Priya Nair | 31900 |
What this exercise teaches (contains the answer)
IFS stops at the first condition that comes back TRUE, so the order you list them in is the logic: testing 25000 before 10000 is what keeps Diego's $24,999 out of the top tier and Priya's $31,900 out of the middle one. Reverse the order and everyone at or above the lowest bar would match it first and never reach the tier meant for them. Keeping the rate in its own column, rather than folding the multiplication into the IFS formula itself, leaves it visible as a number a manager can point to when a rep asks why their commission looks off.