Rank a sales contest without reordering the table
RANK reports where a number stands without moving it, and LARGE finds who's in second without sorting anything.
Task:
You run an inside-sales contest for a six-person team. This quarter's numbers are in front of you and management wants each rep placed 1 to 6, best first — but the sheet feeds a dashboard that reads rows by employee ID, so the rows themselves have to stay put. Work out each rep's rank in column C. The runner-up also gets a spot bonus worth 5% of the second-highest total, which nobody has a name for yet, so work that out in B8.
Learning Objectives:
- Rank values without disturbing the order the rows are in
- Lock a range with absolute references so filling down doesn't shift it
- Reach for LARGE when you need a specific ranked value rather than every row's position
Solve without hints for +5 XP
Interactive Spreadsheet
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 | Q3 Sales | Rank |
| 2 | Ortiz | 84200 | |
| 3 | Bahri | 91750 | |
| 4 | Kowalski | 78300 | |
| 5 | Nakamura | 102400 | |
| 6 | Silva | 96800 | |
| 7 | Petrov | 89100 | |
| 8 | Runner-up bonus (5% of 2nd place) |
What this exercise teaches (contains the answer)
RANK defaults to treating the biggest number as rank 1, which is exactly what a leaderboard wants, so nothing needs to be sorted for the position to be correct — Nakamura's 102400 is the top figure and RANK reports it as 1 wherever the row happens to sit. The absolute reference is what makes filling down safe: without it, Bahri's rank formula would compare her total against B3:B8 instead of B2:B7, quietly ranking her against a different six rows than everyone else. LARGE sidesteps the ranking column entirely for the bonus — it doesn't care who came second, only what the second-highest number was, which is all the payroll calculation actually needs.