RANK
Shows a number’s place in a list: where a sales rep ranks, what position a product holds by revenue.
RANK(number, ref, [order])- [ ]optional part
- number
- The number whose place you’re looking for, for example sales in B2.
- ref
- The whole list to rank against, with $ signs: $B$2:$B$7.
- order
- Omitted or 0: the largest gets first place. 1: the smallest gets first place.Optional
Return value
The number’s place in the list. Without the third argument, place 1 goes to the largest. Equal numbers share a place. If the number isn’t in the list, you get a #N/A error.
Examples
Sales rep ranking
fx
A
B
C
D
1
2
3
4
5
6
7
8
9
Rep
Sales, $
Place
Johnson
561800
Miller
469400
Davis
605200
Wilson
354400
Moore
469400
Taylor
412300
Sheet1
Details
RANK checks how many numbers in the list are greater than the given one and returns its place: the largest gets 1. You don’t need to sort the table.
The third argument flips the direction. If you put 1, the smallest number gets first place. You need this for time, price, number of errors.
Equal numbers get the same place, and the next place is skipped: after two second places comes fourth. Newer versions of Excel have RANK.EQ, which works the same way.
Common mistakes
- A list without $ signs: when you drag the formula, it shifts down, and the places come out wrong.
- For time or price, the third argument 1 is missing: the best result gets last place.
- A number that isn’t in the list gives a #N/A error.