Excel Tip of the Week: RANK

Today’s topic is the RANK function.

RANK allows you to return the rank of a number among a specified range of numbers. RANK takes three inputs; two required and one optional:

Number is the single number you want ranked
Ref is the range of values you would like the above number to be ranked against
[order] is optional and is a “1” or “0” option. 0 = Descending and 1 = Ascending. If you leave it blank, Excel assumes “0″ and ranks the largest number first. If you type “1” then Excel will rank the largest number as last.

This function can come in handy when your data is not set up in a way where you can sort it to figure out the highest or lowest number. Also, it can help if you would like to rank a number from outside your main data set against your main data set. Let’s walkthrough a [very simple] example:

1. Download the example spreadsheet here
2. Move your cursor to cell C4
3. Type in: =RANK(D4,$D$4:$D$16,0) **Note the absolute cell references**
4. Copy the formula down
5. That’s it. Good job.

Now it is simple to see which number is largest, smallest, and everything in between.

But what happens when there is a tie? Great question. Excel will assign all the tied values the same value, but then the subsequent values will be ranked based on the number of values ahead of it. For example:

Notice how there is three instances of 65. Each of those values are ranked 2^nd, but then the next highest value, 48, is ranked 5^th because there are 4 values ahead of it.

Nerd Alert! Stop reading if you don’t care about Excel minutia.

Technically, the RANK function has been replaced in Excel 2010 and 2011 by two new RANK functions: RANK.EQ and RANK.AVG while the RANK function still exists for backwards compatibility reasons.

RANK.EQ is essentially the same as the RANK function explained above. The RANK.AVG function works essentially the same as well except for how it deals with ties. Instead of returning the highest rank of all the tied values, it averages the ranks.  So in the above example the three 65’s would receive a rank of (2+3+4) ÷ 3 = 3.