IF that returns a calculation
The answer to an IF can be a sum, not just a word.
Task:
Reps earn 5% commission on their sales, but only once they pass 10,000 in the month; below that they earn nothing. Put each rep's commission in C2:C6.
Interactive Spreadsheet
Hints
+5 XPno-hints bonusSolve it on your own to keep the bonus. Each hint gets one step closer to the formula.
Variations of IF
0/4The same formula in the other shapes it takes at work.
- Basic IF Function
Make the sheet decide, instead of deciding row by row yourself.
CoreIntermediate - IF that returns a calculation
The answer to an IF can be a sum, not just a word.
Intermediate - IF for an empty cell
Test for "" before calculating with a cell that might not be filled in yet.
Intermediate - IF against another cell
Each row carries its own threshold; compare to the cell, not a typed number.
Beginner
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 | |
|---|---|---|---|
| 1 | Rep | Sales | Commission |
| 2 | Hana | 12400 | |
| 3 | Idris | 9800 | |
| 4 | Jo | 10000 | |
| 5 | Kofi | 15600 | |
| 6 | Lena | 10001 |
What this exercise teachesMay contain the answer
Returning 0 rather than the word "None" keeps the column numeric, so the commission total at the bottom of the sheet still adds up. Jo, on exactly 10,000, earns nothing — whether the threshold is > or >= is a policy question, and the formula has to say which.