Units still needed to hit quota (IF, never negative)
Test case
1
Reset
Submit
A
B
C
D
E
F
G
1
2
3
4
5
6
7
8
9
10
○ Ready Enter formula in highlighted cells, then click Submit.
Test case
1
Reset
Submit
A
B
C
D
E
F
G
1
2
3
4
5
6
7
8
9
10
○ Ready Enter formula in highlighted cells, then click Submit.
The problem
Problem statement
The unit quota is in E1 and reps' unit sales are in B2:B4. Row 2 has already sold some; put how many more units they need in the Still needed cell E2.
Someone who is already over quota needs 0 more, not a negative number.
Problem data
Show the sheet as a table (selectable, copyable)
| A | B | D | E | |
|---|---|---|---|---|
| 1 | Rep | Units sold | Quota (units) | 500 |
| 2 | Ada | 320 | Still needed | |
| 3 | Ben | 520 | ||
| 4 | Cleo | 500 |
Notes
Quota minus sold is only correct while the rep is behind. Past the quota the subtraction goes negative, and a target cannot be negative, so the formula has to floor it at zero — either with IF or with MAX(0, …).
How to approach
Your answer goes in E2.
- Target cell is E2
- Quota minus sold units
- Negative means they are already over quota → 0
- Shape: =IF(?>?, ?-?, ?)
Function guide
FAQ
How do I show units remaining to quota?
=MAX(0, quota - sold). The MAX stops the answer going negative once someone passes their target.
Why does my gap show -20?
The rep is over quota. A plain subtraction keeps the sign; floor it at zero with MAX or IF.