RANK Function
Position in the table, worked out rather than eyeballed.
Task:
The quarterly figures are in and the regional director wants the branches placed 1 to 5, best first. The rows are in alphabetical order and are staying that way, so work the position out in column C.
Learning Objectives:
- Rank values without reordering rows
- Lock a range with absolute references
- See why a wrong rank still looks believable
Solve without hints for +5 XP
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 | Branch | Quarterly revenue | Rank |
| 2 | Leeds | 418500 | |
| 3 | Cardiff | 372000 | |
| 4 | Glasgow | 455200 | |
| 5 | Bristol | 389750 | |
| 6 | Norwich | 401300 |
What this exercise teaches (contains the answer)
Sorting would answer this too, and would destroy the alphabetical order the sheet is circulated in. RANK reports the position while leaving the rows where they are, which is what you want on anything someone else reads. The absolute reference is the part that catches people: without it, row 3 ranks itself against B3:B7 and the numbers come out plausible and wrong.