Here are a few things to know about using the RANK function in Excel:
You can download the file here to follow along. If you get a preview just look for the download arrow in the upper right corner.
In newer versions of Excel you will see 3 types of RANK listed:
RANK.AVG – If there is a tie, this will return the average of the rankings
RANK.EQ – If there is a tie, this will return the highest of the rankings
RANK – This if for 2007 or older versions of Excel
Since I am using Excel 2013, we will look at the first two choices.
Here is a list of numbers from 1 to 15, randomly generated using RANDBETWEEN.
In column B I will enter the RANK.AVG function and in column C, the RANK.EQ function. For either, the syntax is =RANK.AVG or RANK.EQ(number,ref,[order]), as defined as follows:
number = the specific number of the list you are ranking
ref = the range or reference you are comparing the number to
[order] = this is optional – 0 or omitted is descending order, any non-zero value is ascending order
Here is the RANK function for each and the formulas entered:
Note that I didn’t enter the optional [order] number, so these are in descending order. If I sort the numbers, it makes it easier to see how Excel handles ties in each situation:
Notice how in RANK.AVG Excel averaged 1 & 2 for number 15 and gave them both a 1.5, while RANK.EQ ranked them both as tied for the first position. If I add a number 1 as the [order] option, it will reverse the rankings:
And that’s how RANK works in Excel. Pretty simple but possibly quite useful!