Logical Functions with Mathematics
A threshold that is itself a calculation, not a fixed number.
Task:
Commission runs on three tiers: 10% for beating target, 5% for landing within 90% of it, nothing below that. Every rep carries a different target, so the 90% mark has to be worked out per row rather than typed in. Put each rate in column D.
Learning Objectives:
- Nest one IF inside another
- Calculate a threshold instead of hardcoding it
- Order tiers so the first match is the right one
Solve without hints for +5 XP
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 | Target | Commission |
| 2 | Nadia Ellis | 12400 | 10000 | |
| 3 | Ben Ortiz | 10000 | 10000 | |
| 4 | Cara Lindqvist | 8800 | 10000 | |
| 5 | Femi Adeyemi | 15000 | 12000 |
What this exercise teaches (contains the answer)
Writing 9000 into the formula would work for the three reps on a 10,000 target and quietly underpay the one on 12,000. Calculating the threshold from the row keeps the rule correct when targets are reset next quarter. Note the rep who landed exactly on target takes 5%, not 10% — the top tier says beat it, and > means what it says.