Excel rankings with ordinal numbers, joint, equal and more
Here’s a look at two things that you’d expect Excel to handle quickly and easily, but doesn’t. Showing a ranking of items with equal ranks shown (3rd equal etc) and ordinal numbers (1st, 2nd, 3rd etc) or ordinal names (first, second, third etc).
All are possible in Excel but strangely not provided by Microsoft directly.
Here’s what we mean. The ‘Rank’ column at left is easily done in Excel, you can create an order for scores from an exam, race or whatever you like. But it only shows plain digits. Excel’s Rank/Rank.EQ function to show order, first, second etc.
The other columns show many other ways to present the same raw number. These are useful when Excel cells are being linked into a Word document or Mail Merge (for example, reports cards or certificates) or just to make your worksheet more readable and professional.
Over a series of Office-Watch.com articles we go through these variations on rankings and how to make them.
Joint or Equal
The examples we’ve seen for handling equal rankings didn’t go far enough. A proper wording shows the difference between joint (two ranks the same) or equal rankings (three or more equal). Joint vs Equal rankings with Excel’s Rank() and Rank.EQ()
Ordinal Rank – 1st, 2nd, 3rd etc
The ‘Ordinal Rank’ column, 1st, 2nd etc you’d expect to be provided by Microsoft. Either as an in-built function or a number format. After all, Excel has many different number formats already, so ordinal numbers would seem really obvious – but no. Ordinal Numbers in Excel, 1st, 2nd, 3rd etc.
Combining Ordinal rank and equality
The other columns demonstrate various ways to combine ordinal numbers or words with the joint/equal indication. Many Ordinal RANK() options in Excel with joint, equal rankings, words and more
‘Better Rank’ shows when there’s two or more entries in equal place. Excel gives you two ways to detect equal ranks, we’ll show you both.
‘Even Better Rank’ differentiates between joint and equal rankings using a custom function. The function is needed to handle many rows with the same equal ranking.
‘Rank Names’ puts the ranks into words. This is especially useful if the Excel cells are being linked into a Word document or Mail Merge (for example, reports cards or certificates).
‘Olympics etc.’ gives you the Gold, Silver, Bronze and fourth place in names with equal/joint
Fun Rank Names
Finally ‘Fun Ranks’ is a bit of fun to show what’s possible. Many Ordinal RANK() options in Excel with joint, equal rankings, words and more
These options aren’t trivial or pointless. They can make your worksheets more professional. With Excel as the data source for a Word mail merge or web page display the ordinal or word variations are very useful indeed.
Office Watch has the latest news and tips about Microsoft Office. Delivered once a week.