Using The Rank.AVG and Rank.EQ Functions In Excel 2012

Excel 2012, Excel 2013

As I continue to look into Excel 2012, there are a few changes or improvements that were made to some of the more common functions. Today, I decided to take a look into the rank function which is very used in excel. First, let’s create a simple situation. Suppose that you have a list of a few hundred students and their grades in a final exam. Then, you’re looking to get the top 5 students. In my example, I’ll only have 7 students for simplicity purposes. You can see my list of students here:

Then, I’m trying to use the “rank” function to determine the top few students. Remember that my list could have a few hundred or a few thousands students.

In Excel 2012, the rank function was split into two:



The main difference is that when two lines have the same “number”, they will react differently:

Rank.EQ = if 2 students have the “top grade”, they will both have a rank of “1”. You can see the result here:

Rank.AVG = if 2 students have the “top grade”, they will both have the average rank. You can see the result here:

I guess your preference would depend on what you’re trying to do but I’d generally prefer using the “Rank.Eq” function. Still, it’s not perfect. Just look at what happens when I try to use the vlookup function to look for the the top 5:

As you can see, I have a problem. What I generally try to do is to avoid “ties” by adding a fraction:

This way, I get no “ties”. That being said, it’s not perfect, but here is my result:

The most important part is understanding the difference between the 2 “Rank” functions in Excel 2012 and how you can use them.

Don’t hesitate to ask if you have any questions!


Look Good at Work and Become Indispensable Become an Excel Pro and Impress Your Boss