In Excel, there are 3 Rank functions that you will see. Below is a summary of each one:
RANK: This is an old format of the Rank function that was used in previous versions of Excel. It’s still available in the most recent versions of Excel for backwards compatability purposes, but the newer versions should be used in most cases.
RANK.EQ: Returns the rank of a number in a list of numbers. If more than one value has the same rank, the top rank is displayed (EQ stands for Equal).
RANK.AVG: Returns the rank of a number in a list of numbers. If more than one value has the same rank, the average rank is displayed (AVG stands for Average).
In our examples, we’ll use the RANK.EQ function, but the same format works for each function.
Syntax
The RANK functions require two criteria and have an optional criteria as well:
Number: The number whose rank you want to find.
Ref: List of numbers that will determine the rank.
Order (optional): A number to specify how to rank the number. If this value is 0 or left omitted, the rank will start with 1 (i.e. 1 will be the largest number). Any other value that’s not 0, the list will end with 1 (i.e. 1 will be the smallest number).
Examples
Let’s say we have a list of people and their test scores and we want to rank them. Below shows a screenshot of how we would structure the RANK.EQ function. Note that the Ref uses absolute references so that when we pull down the formula, the Ref cells will stay the same.
Here’s what this formula would return for the whole table. Note that the two 95 scores both have a 2 ranking. Then the next score is a 4 (3 is skipped).
If we wanted to order the list in the opposite order, we could add “,1” at the end of the function and it would show the top score with the top rank (4 in this case):
See the video above for additional aspects of this funtion.