Many Ordinal RANK() options in Excel with joint, equal rankings, words and more
Office for Mere Mortals helps people around the world get more from Word, Excel, PowerPoint and Outlook. Delivered once a week. free.
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.
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.
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)","")
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.
Rank Ordinal Names
Another surprising omission from Excel, aside from ordinal numbers, is ordinal words like First, Second, Third etc.
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.
Fun Rank Names
Who says Excel can’t be fun? With a little work you can add more descriptive, even fun, labels.
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.
Function OrdinalRankName(pNumber As String) As String
' ONLY values up to 11 English only
Select Case CLng(pNumber)
OrdinalRankName = "First"
OrdinalRankName = "Second"
OrdinalRankName = "Third"
OrdinalRankName = "Fourth"
OrdinalRankName = "Fifth"
OrdinalRankName = "Sixth"
OrdinalRankName = "Seventh"
OrdinalRankName = "Eighth"
OrdinalRankName = "Ninth"
OrdinalRankName = "Tenth"
OrdinalRankName = "Eleventh"
OrdinalRankName = "Twelveth or more!!!!!"
Function RankName(pNumber As Integer, pTopRank As Integer) As String
' Changes to words for top and bottom ranked entries
' Top and middle rank entries
Select Case pNumber
RankName = "Very Best"
RankName = "Second Best"
RankName = "Almost made it"
RankName = "Middle of the road"
' For bottom ranked item
If pNumber = pTopRank Then
RankName = "Dead Last"
' For second bottom ranked item
If pNumber = pTopRank - 1 Then
RankName = "Second Last"
' For third bottom ranked item
If pNumber = pTopRank - 2 Then
RankName = "Third Last"
Office Watch has the latest news and tips about Microsoft Office. Independent since 1996. Delivered once a week.