Many Ordinal RANK() options in Excel with joint, equal rankings, words and more

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

In a previous article we looked at options for showing ordinal numbers in Excel, now we’ll go a step further to cope with joint or equal results plus special wording.

On the left are the basic ordinal rankings, the next column has text to show equal rankings while column M shows joint and equal rankings.  The last two columns have words instead of values.

many ordinal rank options in excel with joint equal rankings words and more microsoft excel 16434 - Many Ordinal RANK() options in Excel with joint, equal rankings, words and more

These variations might seem trivial but they can be very useful when merging with Word to make a seamless document.

Here’s some examples with the field/cell from Excel highlighted. The first line would be from a plain RANK() cell.  The others look more professional.  The last two don’t look like part of a mail merge at all.

many ordinal rank options in excel with joint equal rankings words and more microsoft excel 16436 - Many Ordinal RANK() options in Excel with joint, equal rankings, words and more

IMPORTANT: These tips use the custom function OrdinalRank() described in Ordinal Numbers in Excel, 1st, 2nd, 3rd etc. Add that function before trying these variations.

Ordinal Rank with equal

Showing equal rankings just needs the COUNTIF() to separate solo rankings from others.

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

many ordinal rank options in excel with joint equal rankings words and more microsoft excel 16437 - Many Ordinal RANK() options in Excel with joint, equal rankings, words and more

Ordinal Rank with joint and equal

This uses CHOOSE() in addition to the previous CountIF().  As noted in an earlier article, Choose() is limited to the number of equal rankings you add.  Our example handles up to 5 equal rankings.

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

If you need just a few more equal rankings (6, 7 or 8 equal entries) just add more ‘ ,” (equal)” ‘  to the end of the Choose()  .To cope with an unlimited number of equal rankings, you need a custom function.

many ordinal rank options in excel with joint equal rankings words and more microsoft excel 16438 - Many Ordinal RANK() options in Excel with joint, equal rankings, words and more

Rank Ordinal Names

Another surprising omission from Excel, aside from ordinal numbers, is ordinal words like First, Second, Third etc.

many ordinal rank options in excel with joint equal rankings words and more microsoft excel 16439 - Many Ordinal RANK() options in Excel with joint, equal rankings, words and more

Another custom function is needed, a variation on the OrdinalRank() we’ve used before.

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

The code for OrdinalRankName() is at the bottom of this article.  This code is limited to 11th place, add more if you need them.

many ordinal rank options in excel with joint equal rankings words and more microsoft excel 16440 - Many Ordinal RANK() options in Excel with joint, equal rankings, words and more

Fun Rank Names

Who says Excel can’t be fun?  With a little work you can add more descriptive, even fun, labels.

many ordinal rank options in excel with joint equal rankings words and more microsoft excel 16441 - Many Ordinal RANK() options in Excel with joint, equal rankings, words and more

Another custom function and another variation on the OrdinalRank() function we’ve used before.

The code for RankName() is at the bottom of this article.

img 5a5e51698e5f6 - Many Ordinal RANK() options in Excel with joint, equal rankings, words and more

OrdinalRankName()

Function OrdinalRankName(pNumber As String) As String

' Office-Watch.com

' ONLY values up to 11  English only

Select Case CLng(pNumber)

    Case 1
    OrdinalRankName = "First"

    Case 2
     OrdinalRankName = "Second"

    Case 3
    OrdinalRankName = "Third"

    Case 4
    OrdinalRankName = "Fourth"

    Case 5
    OrdinalRankName = "Fifth"

    Case 6
    OrdinalRankName = "Sixth"

    Case 7
    OrdinalRankName = "Seventh"

    Case 8
    OrdinalRankName = "Eighth"

    Case 9
    OrdinalRankName = "Ninth"

    Case 10
    OrdinalRankName = "Tenth"

    Case 11
    OrdinalRankName = "Eleventh"

    Case Else
    OrdinalRankName = "Twelveth or more!!!!!"

End Select

End Function

RankName()

Function RankName(pNumber As Integer, pTopRank As Integer) As String

' Office-Watch.com

' Changes to words for top and bottom ranked entries

' Top and middle rank entries

Select Case pNumber

    Case 1
    RankName = "Very Best"

    Case 2
    RankName = "Second Best"

    Case 3
    RankName = "Almost made it"

    Case Else
    RankName = "Middle of the road"

End Select

' For bottom ranked item

If pNumber = pTopRank Then
    RankName = "Dead Last"
End If

' For second bottom ranked item

If pNumber = pTopRank - 1 Then
    RankName = "Second Last"
End If

' For third bottom ranked item

If pNumber = pTopRank - 2 Then
    RankName = "Third Last"
End If

End Function

subs profile e1563205311409 - Many Ordinal RANK() options in Excel with joint, equal rankings, words and more
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