All About RANK In #Excel

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.

rank1

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:

rank2

rank3

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:

rank4

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:

rank5

And that’s how RANK works in Excel. Pretty simple but possibly quite useful!

Happy Excelling!

Related Post

Free Download!

Subscribe to Download Your FREE Copy of
"My 70+ Favorite Excel Keyboard Shortcuts" Today!

, ,

No comments yet.

Leave a Reply