Category Archive 'Excel 2012'

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:

Rank.EQ

Rank.AVG

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!