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
