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
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

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.

joint vs equal rankings with excels rank and rank eq microsoft excel 16371 - Joint vs Equal rankings with Excel's Rank() and Rank.EQ()

And you need to add the rank as well:

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

joint vs equal rankings with excels rank and rank eq microsoft excel 16372 - Joint vs Equal rankings with Excel's Rank() and Rank.EQ()

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()

joint vs equal rankings with excels rank and rank eq 16369 - Joint vs Equal rankings with Excel's Rank() and Rank.EQ()

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

subs profile e1563205311409 - Joint vs Equal rankings with Excel's Rank() and Rank.EQ()
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