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.
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 | |
|---|---|---|---|
| 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 teachesMay contain 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.