Using Ranking Functions in 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 14th
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
This function replicates the older RANK() behaviour, and should be used when you previously used RANK. It takes the same set of parameters
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.
But, as you can see, the ’69’ and ‘2.3’ values are different.
For 69, it’s received 5.5, which is an average of 5th & 6th places. For 2.3, it’s received 13, which is an average of 12th, 13th & 14th places
And here they are together…