Excel functions / Maths & statistics
RANK
Returns the position of a number within a list, such as 1st, 2nd or 3rd.
Syntax
=RANK(number, ref, [order])Arguments
number | The value to rank. |
|---|---|
ref | The list to rank it against. |
order | Optional. Leave out or 0 for largest first; 1 for smallest first. |
How it works
RANK tells you where a value places in a list. By default the biggest number is ranked 1, which suits sales leagues and scores. Use order 1 for times or costs, where smaller is better.
Example
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Region | Rep | Sales | ||
| 2 | East | Ana | 500 | 2 | |
| 3 | West | Ben | 300 | ||
| 4 | East | Cal | 250 | ||
| 5 | West | Dee | 700 | ||
| 6 |
Result: 2. Ana's 500 is the 2nd highest. The $ signs keep the list fixed when the formula is copied.
Tips
- Lock the list with $ before copying a RANK formula down the column.
Practise RANK
Try it yourself in a real spreadsheet, with instant feedback on every answer.