Excel has a way to rank a series of values, showing which is top, middle and bottom. As usual, what appears to be a simple thing has extra features to cope with the real world like equal placings.
Here’s a simple ranking. As you can see, Excel has ranked the Value entries from 1 to 9. The Rank column goes from highest value to lowest for things like exam results, ‘Reversed Rank’ goes from lowest to highest for races, golf scores etc.
Three RANK functions
There are three different RANK functions in modern Excel.
Rank.EQ() returns the rank integer either descending or ascending order
Rank.AVG() is almost the same except that if two items have equal rank, it returns the average rank.
RANK() is the older Excel 2007 and before version of Rank.EQ(). Use this if you need backward compatibility.
For this article we’ll use Rank.EQ() / Rank() since that’s the most commonly used.
PercentRank() etc
OK, OK, yes there are three more ‘Rank’ functions, PercentRank(), PercentRank.INC() and PercentRank.EXC(). We’ll leave them for another time ….
Rank.EQ() or Rank()
The basic format of Rank.EQ() or Rank() is
RANK.EQ(number,ref,[order])
Number – the number whose rank you want to find. Usually an adjacent cell in a nearby column.
Ref – the range of values to rank within. Can be a cell range, named range or table range. Non-numeric values in Ref are ignored. Usually a nearby column of cells.
Order – Optional. 0 = descending, the default. 1 = ascending.
Here is Rank.EQ at work in a table =RANK.EQ([@Score],[Score]) It takes the current row score [@Score] and compares to all the scores [Score].
If it wasn’t a table, the formula =RANK.EQ(B2,B2:B10) would do the same thing.
Reversed Rank
Switching the order is simple, just add the third parameter.
=RANK.EQ([@Score],[Score],1) or =RANK.EQ(B2,B2:B10,1)
