Joint vs Equal rankings with Excel’s Rank() and Rank.EQ()

Office for Mere Mortals
Your beginners guide to the secrets of Microsoft Office
Invalid email address
Give it a try. You can unsubscribe at any time.

If you’d like to be really clever with Rank/Rank.EQ in Excel, here’s how to show different words for joint rankings (only two with the same rank) or equal rankings by three or more.

It’s a small difference but can make your reports or merged documents look a lot more professional.

Use the Choose() function to choose between unshared, joint and shared equal rankings.

=CHOOSE(COUNTIF([Rank],[@Rank]),"","Joint", "Equal")

Choose() uses the first parameter (the number of rankings that have the same result from CountIF) and returns the string in the next places.  If the value is 1, the second parameter and so on.

And you need to add the rank as well:

=[@Rank]&" "&CHOOSE(COUNTIF([Rank],[@Rank]),"","joint", "equal")

That Choose() formula only copes with two or three rows with the same rank. In practice, you need to add more parameters to handle four or more equal place getters.

This will handle up to 8 rows/places with the same ranking.

=CHOOSE(COUNTIF([Rank],[@Rank]),"","Joint", "Equal","Equal","Equal","Equal","Equal","Equal"))

Custom Function alternative for Rank()

For a more comprehensive alternative, try this custom function RankName()

Function RankNameSuffix(pNumber As String) As String
' returns a suffix depending on the number of equal rankings.

Select Case CLng(VBA.Right(pNumber, 1))

    Case 1
    RankNameSuffix = ""

    Case 2
    RankNameSuffix = "joint"

    Case Else
    RankNameSuffix = "equal"

End Select
End Function

Want More?

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