Rank sales reps by quarterly revenue and assign a bonus tier with RANK and nested IF
RANK turns a raw revenue figure into a position among the rest of the team without touching the order the rows sit in, and that position is what nested IF can then test to sort reps into a tier.
Task:
You manage a five-person regional sales team, and the roster stays listed in the order HR hired them in — nobody wants it reshuffled by revenue just to see who's winning. Bonus tiers are decided by how each rep's quarterly revenue stacks up against the other four: the top rep gets "Top performer", the next two get "On target", and the bottom two get "Needs coaching". In column C, use RANK to find where each rep's revenue in B2:B6 places against the whole team, 1 for the highest. In column D, use nested IF on that rank to assign the tier. Fill in rows 2 through 6.
Interactive Spreadsheet
Hints
+5 XPno-hints bonusSolve it on your own to keep the bonus. Each hint gets one step closer to the formula.
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 | Q3 Revenue | Rank | Bonus Tier |
| 2 | Elena Cho | 142000 | ||
| 3 | Marcus Webb | 89500 | ||
| 4 | Priya Shah | 176000 | ||
| 5 | DeShawn Lee | 104000 | ||
| 6 | Omar Haddad | 121000 |
What this exercise teachesMay contain the answer
RANK(B2,$B$2:$B$6) answers a different question than the revenue figure next to it does — not how much Elena Cho sold, but where that number lands once it's measured against the other four reps in the range. The $ signs are what let that comparison survive being copied down: without them, row 4's formula would rank Priya Shah's revenue against B4:B8, a window that no longer holds the same five reps the one above it did. Once rank is a plain number in column C, the nested IF in column D stops caring about revenue at all — it only ever asks about rank. Catching rank 1 before testing <=3 matters because the top rep's rank of 1 would also pass that second test; checking the narrower case first is what keeps a top performer from quietly landing in the "On target" bucket alongside ranks 2 and 3.