Using Excels Rank functions
Excels Rank functions have evolved over time, and give you the ability to ranks values in a list or column based on their value compared to other values in the same list or column.
In Excel 2007 and previous versions, the function you would use to rank value is Rank and the syntax is:
=RANK(Number, Ref, [Order])
Where:
Number is the number you require the ranking for
Ref is the array, list or column of cells that contain all the value from which you want to calculate the ranking posting.
Order is how you define the ranking. 1 is for ascending order and 0 is for descending order.
In Excel 2010 and later versions, this standard rank function has been replaced with Rank EQ and Rank Average.
Rank EQ and Rank will do exactly the same thing. The only difference is, if you wish to share your workbook with others using Excel 2007, you must use Rank and Rank EQ is not supported.
The syntax for Rank EQ is
=RANK EQ(Number, Ref, [Order])
Where Number, Ref and Order play the same roles as they did in Rank.
The syntax for Rank Avg is also the same =RANK AVG (Number, Ref, [Order])
When you use Rank or Rank EQ and you have duplicate values, the duplicates are all given the same ranking. Lets say you had a value of 10 and it is found 3 times in the list. 10 is the second highest value in the list.
The Ranking based on descending order will be 2. But you will find a ranking of 2 for each of the 3 occurrences of 10. After this the next value in the ranking will be 5.
So how does Rank Avg work then? Well, it does not get an average of the values (3*10=30/3=10…this would be the average right….).
Instead it gets the average of the ranking, so if the ranking positions of 2,3,4 are the next available positions, then the ranking will be the average of this. (Which is 3 in this case).
Sometimes you don’t want to have the same ranking for each duplicate value. How would you go about this using Excel?
We can create a unique Ranking in Excel by combining the Rank Function with CountIF.
In the video above we use the following logic
First we will rank the values. This will give us the ranking which we will call X
Then we will count if the current value has occurred in any of the cells from the current up that have already been ranked. We will call this Y.
Next we can then add X and Y.
However s the value we wish to rank is included in X and Y we now need to deduct 1 to remove the double count.