This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(128, 8%, 22%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EA%3C/text%3E%3C/svg%3E)
Hello,
I am wondering if anyone knows (or knows of any resources that describe) how to create an ordinal number format in MS Excel.
I am well aware of how to reformat numbers as text to create the ordinal number effect, but what I would like to do is have the numberdisplay as an ordinal number (e.g., "1st", "2nd", "3rd", etc.) while still retaining its numeric value (1, 2, 3, etc.).
The reason for this request is to be able to perform numeric functions (e.g., MIN, MAX, AVERAGE, etc.) on an array of ordinal numbers.
I have a spreadsheet that lists various athletes' scores and the ranking for each score. I would like to be able to easily determine, for example, each athlete's best ranking by using a MIN() function on the list of ordinal numbers. Currently the only way I can think to do this is by having the rankings as regular cardinal numbers.
I suppose it would be possible to have an adjacent, hidden column with the numeric value of each ranking, but I thought it might be more elegant if there was a way to change the format of the number without changing its value.
Any help is much appreciated!
Regards,
Craig
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(307.2, 41%, 39%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EPE%3C/text%3E%3C/svg%3E)
My reply of April 28, 2014 applies to both your post and that of all the others that output the ordinal in a different cell. That is, they don't meet the OP's specification:
have the number display as an ordinal number*(e.g., "1st", "2nd", "3rd", etc.) while still retaining its numeric value (1, 2, 3, etc.).*
Suppose the number is X
=IFERROR(CHOOSE(MIN(MOD(X*AND(X<>{13,12,11}),10),4),"st","nd","rd"),"th")
Regards
Brian
Thanks Andreas
Eureka!! Thanks for that. It works!
Daleman9
I had a list of 100 and that I had to select the first 10, the remainder are non-qualifiers.
the remainder should show blank.
As all your formulas show the ordinals, I take the shortest and improve it a little. This one shows only the ordinal numbers from 1 to 10:
=IF(OR(A1<=0,A1>10),"",A1&MID("thstndrdth",MIN(9,2*RIGHT(A1)*(MOD(A1-11,100)>2)+1),2))
Andreas.
Andreas
Sorry, but in my original post I said that I had a list of 100 and that I had to select the first 10, the remainder are non-qualifiers. My formula selects the top 10 but so far I'm unable to express those 10 in Ordinal mode without the other 90 showing "0th" - better that the remainder should show blank. Hope this makes it clear and you can help.
Daleman9