Ranking a List

Rank.EQWhat do you do if you need to determine the order of your golf score or perhaps sales volume? The easiest way obviously is to sort your data in ascending or descending order but sometimes you don't have that option. If you can't reorder your data then you can use RANK().
In Excel 2010, they have renamed this function to RANK.EQ() and Microsoft recommends that you begin using it but RANK() still works.



The sytax for both RANK and RANK.EQ are:
Syntax:

• Number is the cell or number whose rank you want to find
• Ref is the array (list of numbers)
• Order is optional. The default is 0 and that means descending order

In my first example here, I used the RANK function and looked at the order of the sales in Column B. Sharon down at Row 9 has the highest sales followed by Sam at Row 5. Now the problem... Both Joe's sales and Kelsey's sales are ranked as 3 and there is nothing ranked 4th. Because there were so many complaints about this, Microsoft add two new functions in Excel 2010 - RANK.EQ and RANK.AVG.

As you can see from the illustration below, RANK.EQ  has the exact same result as RANK. Joe and Kelsey are both still ranked at 3rd and there is no 4th ranking.  Sorry, I have no clue why Microsoft would just rename a function.

Microsoft did come up with a new ranking function in Excel 2010 too. It is called RANK.AVG.

In this case, if more than one value has the same rank then the rank is averaged. So, now Joe and Kelsey are each ranked as 3.5 rather than 3 and there is still no 4th ranking.

So, RANK.EQ or RANK.AVG may work for you, particularly if you do not have values that are the same however if you wanted to do a Vlookup or an Index Match based upon the rank - you would obviously have a problem since the lookup isn't going to know that two of your values have the same ranking and that rank 4 is missing. To compensate, you can include a CountIf to identify any duplicates and get a useful ranking.

In Cell I2, the formula does not include the CountIF formula since it is the first value and there is nothing to compare it to. The formula is just =RANK.EQ(B2,$B$2:$B$11,0)

In Cell I3, I changed the formula to =RANK.EQ(B3,$B$2:$B$11,0)+COUNTIF($B$2:B2,B3) which you can then copy down.

The RANK.EQ or RANK formula assigns the rank and then the Countif looks at the cell(s) above and compares it. The CountIF returns a value of 0 if it does not find a duplicate. It assigns a value of 1 if it finds a duplicate. So, when the COUNTIF gets to row B7, it determines that value of 3 already exists in the list so its resulting answer is 1.

This formula result of 1 is added to the Rank value of 3 and Kelsey is now assigned a ranking of 4
(If Excel had  found a 2nd duplicate ranking (3 cells containing the same ranking) the CountIF formula result would be 2).

If you want a copy of this file, click here.

Also please note the use of the absolute relative references and the relative references otherwise when you copy it down, it will not work.