Sales Leaderboard with RANK
Your task: In C2:C7, rank each salesperson by sales (1 = best), and in C9 find the gap between 1st and 2nd.
Full instructions, scenario and goal
Scenario
A regional manager wants a leaderboard of this quarter's sales. Two salespeople finished with exactly the same figure. The manager needs each person's rank, and the margin by which the winner beat the runner-up for the newsletter.
Your task
- In C2, rank the first salesperson by sales, where 1 is the highest sales.
- Copy the formula down to C7. Make sure every row still compares against the whole list B2:B7.
- In C9, calculate the gap between the highest sales and the second-highest sales.
- Select Check Answer when you are done.
You are done when
- The best salesperson ranks 1 and the lowest ranks last.
- Tied salespeople share a rank.
- Every rank compares with the whole list.
What you will practise
Rank values against a fixed list, understand how tied values are ranked, and compare first and second place.
The idea in 30 seconds
RANK(number, range) tells you the position of a number inside a list. By default it ranks from the largest (1 = highest); a third argument of 1 ranks from the smallest instead. The range is the same list for every row, so it must be locked with dollar signs when you copy the formula down. Otherwise the range slides down with the formula and each salesperson is compared with a shorter and shorter list.
Spreadsheet
Loading spreadsheet…
Keyboard shortcuts
- Arrow keys
- Move between cells
- Shift + Arrow keys, or drag with the mouse
- Select a range
- Ctrl + Arrow keys
- Jump to the edge of a block of data
- Type, or F2, or double-click
- Edit a cell (F2 switches arrow keys between moving and editing)
- Click or arrow keys inside a formula
- Insert a cell reference after =, +, (, comma and similar
- F4
- Cycle a reference through A1, $A$1, A$1, $A1
- Enter / Tab while editing
- Confirm and move down / right (Shift reverses)
- Esc
- Cancel the edit
- Ctrl + C / X / V
- Copy, cut and paste (formulas adjust like in Excel)
- Delete
- Clear the selected cells
- Ctrl + Z / Ctrl + Y
- Undo and redo
- Ctrl + Enter
- Check your answer
- Tab when not editing
- Leave the spreadsheet
Type your answer in cell C2. Click a cell and type. Enter confirms, Esc cancels, Ctrl+Enter checks your answer.Tap a highlighted cell to type in it, then tap the green tick to confirm. Scroll the sheet sideways if it is wider than your screen.
New here? How an exercise works
- Read the task. Highlighted cells are yours to fill in; the rest are locked.
- Select (or tap) a highlighted cell and type a formula. It starts with =, like =A1+B1.
- Press Check answer. Stuck? Reveal hints one at a time.
The full explanation: the idea behind this exercise
RANK(number, range) tells you the position of a number inside a list. By default it ranks from the largest (1 = highest); a third argument of 1 ranks from the smallest instead. The range is the same list for every row, so it must be locked with dollar signs when you copy the formula down. Otherwise the range slides down with the formula and each salesperson is compared with a shorter and shorter list.
When two values are tied they get the same rank, and the next rank is skipped: two people at rank 4 mean nobody is at rank 5. That is how sports tables work too. To look at the values that sit at specific positions rather than their ranks, use LARGE(range, k); subtracting the second from the first gives the winning margin.
Skills you will practise
- RANK
- LARGE
- Ties
- Absolute references
Complete first: Commission with One Rate Cell
Common mistakes to avoid
- Not locking the list
- Copy =RANK(B2,B2:B7) down and the list becomes B3:B8, B4:B9... so later rows are compared with the wrong cells.
- Ranking from the bottom
- A third argument of 1 makes the smallest value rank 1. Leave it out for a leaderboard.