Excel's Rank/Rank.EQ function to show order, first, second etc.

Office for Mere Mortals
Your beginners guide to the secrets of Microsoft Office
Invalid email address
Tips and help for Word, Excel, PowerPoint and Outlook from Microsoft Office experts.  Give it a try. You can unsubscribe at any time.  Office for Mere Mortals has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy

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.

excels rank rank eq function to show order first second etc 16349 - Excel's Rank/Rank.EQ function to show order, first, second 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].

excels rank rank eq function to show order first second etc microsoft excel 16350 - Excel's Rank/Rank.EQ function to show order, first, second etc.

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

subs profile e1563205311409 - Excel's Rank/Rank.EQ function to show order, first, second etc.
Latest news & secrets of Microsoft Office

Microsoft Office experts give you tips and help for Word, Excel, PowerPoint and Outlook.

Give it a try. You can unsubscribe at any time.  Office Watch has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy
Invalid email address