Pay only on the amount above target
MAX(0, …) turns "only if positive" into arithmetic.
Task:
Reps earn a 10% bonus on whatever they sell above their own target, and nothing if they miss it. In D2:D6 give each rep's bonus.
Hints
+5 XPno-hints bonusSolve it on your own to keep the bonus. Each hint gets one step closer to the formula.
Variations of Logical Functions with Mathematics
0/4The same formula in the other shapes it takes at work.
- Logical Functions with Mathematics
A threshold that is itself a calculation, not a fixed number.
CoreAdvanced - Pay only on the amount above target
MAX(0, …) turns "only if positive" into arithmetic.
Intermediate - A condition that changes the rate
Multiply by the result of an IF to switch a premium on and off.
Intermediate - Progressive bands with MIN and MAX
Each slice of the amount is charged at its own rate.
Advanced
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 | Bonus |
| 2 | Pia | 58000 | 50000 | |
| 3 | Quin | 41000 | 45000 | |
| 4 | Ravi | 62500 | 60000 | |
| 5 | Suki | 30000 | 30000 | |
| 6 | Tariq | 77000 | 55000 |
What this exercise teachesMay contain the answer
=IF(B2>C2,(B2-C2)*0.1,0) is the same rule written twice. MAX(0, x) is the standard way to say "x, but never below zero", and it removes the duplicated subtraction. Quin's miss would otherwise produce a negative bonus — a deduction nobody agreed to.