Two ways to show equal rankings with Excel’s Rank() or Rank.EQ()

Office for Mere Mortals helps people around the world get more from Word, Excel, PowerPoint and Outlook. Delivered once a week. free.

Rank() and Rank.EQ() are fine while each value/score is different but in the real world you’ll see cells with the same value and ranking.  We’ll show you how to detect equal rankings, display a note when there are equal rankings.

Let’s start with an example of equal rankings. See Venus/Vulcan in this example, both have a score of 56 and a joint ranking of 3.

Excel skips rankings when there are equal scores.  For example, there are two 3rd places so Excel skips to 5th place for Gustavus.   Same for the equal 6th place getters so there’s no 7th place and Excel skips to 8th for Daisy.

How to detect equal rankings

How can you detect when there’s an equal ranking?  That’s important in various situations like averaging the price money or using some other criteria to split the difference.

COUNTIF(<Rank>,<Range of Ranks>) will do the job by returning the number of scores that have the same ranking.

Here you can see that there are two rows with joint 3rd rank and three with equal 6th place.  The unique ranks show 1.

Normally you’d not make a like this.  We’ve added it to show our working for the next step.

Showing equal rankings

Now we can detect equal rankings, here’s how to show them.

Add an IF statement to separate the equal rankings (the rank count is greater than one) then add the word ‘equal’.

=IF(COUNTIF([Rank],[@Rank])>1," (equal)","")

In practice you’d put in the rank as well, so you end up with

=[@Rank]&IF(COUNTIF([Rank],[@Rank])>1," (equal)","")

Want More?

Office Watch has the latest news and tips about Microsoft Office. Independent since 1996. Delivered once a week.