Excel track · Exercise 14 of 86 Paid
Rank a list without sorting it
Put a place number on every row of an unsorted list, in either direction.
Intermediate
A team of swim times ranked both ways
Read-only data · screen 1 of 2 · answer in C2
Put Ada's place in the team into C2 using the default order, without sorting the sheet.
| A | B | C | |
|---|---|---|---|
| 1 | Swimmer | Seconds | Place |
| 2 | Ada | 58 | |
| 3 | Ben | 61 | |
| 4 | Cleo | 55 | |
| 5 | Dev | 57 | |
| 6 | Eli | 50 |
Flip the order argument so the smallest time takes place 1, and put Ada's place in C2.
| A | B | C | |
|---|---|---|---|
| 1 | Swimmer | Seconds | Place (slowest first) |
| 2 | Ada | 58 | |
| 3 | Ben | 61 | |
| 4 | Cleo | 55 | |
| 5 | Dev | 57 | |
| 6 | Eli | 50 |
Reference answer
What it does
RANK compares one value against a range and returns the place it takes. The optional third argument sets the direction: 0 or nothing ranks the largest number first, and any other value ranks the smallest first. The range needs anchoring with dollar signs so it does not slide when the formula is filled down.
On this sheet the times run 58 in B2, 61 in B3, 55 in B4, 57 in B5 and 50 in B6. Ranked from the largest down, 61 takes place 1 and Ada's 58 takes place 2. A third argument of 1 turns the order round, and her 58 becomes place 4 with Eli's 50 at the top.
Equal values share a place and the next one is skipped, so two swimmers on 61 are both first and nobody is second. A total built by adding ranks will then miss a number, because a rank names a row and does not count rows.
Syntax
=RANK(number, ref, [order])Worked example
On A team of swim times ranked both ways, =RANK(B2,B2:B6) returns 2.
=RANK(B2,B2:B6)Common errors
| Error | What it means and how to fix it |
|---|---|
#N/A | The value being ranked is not in the range, which happens when the number argument points at a different row from the list. |
#VALUE! | The number argument is text, so there is nothing to compare with the other values. |
Order backwards | A third argument of 1 ranks the smallest value first, so a missing argument flips every place on the sheet. |
At work
A league table showing each swimmer's place on the row they already sit in, and the same sheet ranked the other way for handicaps.