Guided project
Sales commission report in Excel
Pay reps correctly, rank them, and roll up by region.
You will use: IF, ROUND, COUNTIF, SUMIF, MAX, XLOOKUP
Starting data
| A | B | C | D | E | F | G | H | I | J | K | L | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Rep | Region | Sales | Rate | Commission | Rank | Region | Sales | Bonus line | 10000 | ||
| 2 | Ava | East | 12400 | East | ||||||||
| 3 | Ben | West | 8800 | West | Top rep | |||||||
| 4 | Cara | East | 15100 | North | ||||||||
| 5 | Dev | North | 9900 | |||||||||
| 6 | Eva | West | 11250 | |||||||||
| 7 | Finn | North | 7300 |
Build it step by step
Step 1
Reps at or above the bonus line in L1 earn 5%, everyone else 2%. Write the rate in D2, fill to D7.
Step 2
Commission is sales times rate, rounded to cents. Write it in E2, fill to E7.
Step 3
Rank each rep by sales, 1 being highest. Write it in F2, fill to F7.
Step 4
Total sales for each region. Write it in I2 so it fills to I4.
Step 5
Who is the top rep? Put their name in L3.