Using Ranking Functions in Excel

October 2, 2015 0 comments Article Easy, Excel

Master the available options in Excel for ranking you data, including the updated Rank.Eq functions.

Whilst using the RANK functions in SQL can seem a little daunting for a novice SQL user, achieving similar results in Excel in very simple and painless.

This article will cover primarily the newer methods you should use in 2010 onwards, but will also provide a look at the earlier ranking function.

Excel 2007 & Earlier

In Excel 2007 and earlier, you had the single RANK() function. This provided basic ranking functionality, and can still be used in later versions of Excel, but you’re advised to use the new functions.

The syntax is

=RANK([The Number To Rank],[The Range of Numbers],[Ascending or Descending])

In the above example, I want to know the rank of 99 (B4) within the list of values on the left (B4:B17). If you want to drag this formula down to give every value a rank, be sure to anchor your range ($B$4:$B$17)

The ‘Order’ parameter is optional, but lets you decide if you want it to rank based on ascending or descending values. If I chose ‘1’ instead of ‘0’, 99.0 would be ranked 14^th

As you can see, with RANK(), any duplication in values will assign all values the same rank will be assigned the top rank value

Excel 2010 onwards

With 2010, we now have two functions to pick from when we rank

=RANK.EQ()

This function replicates the older RANK() behaviour, and should be used when you previously used RANK. It takes the same set of parameters

=RANK.AVG()

However, we now have RANK.AVG, which does behave a little differently. It’s all in how it handles values with equal ranking. Rather than assigning the top rank to all values, it will give the values an average of the ranking range assigned to them.

Again, it takes exactly the same parameters as before.