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.
This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.
Comments have been turned off. Learn more
This formula will find the string value in I2 in array C3:C385 and display its position in the array as a text string with the proper ordinal ending using nested IF statements.
=IF(I2="","",IF(MATCH(I2,C3:C385,0)=1,"1st",IF(MATCH(I2,C3:C385,0)=2,"2nd",IF(MATCH(I2,C3:C385,0)=3,"3rd",MATCH(I2,C3:C385,0)&"th"))))
- backward compatability - valid point... from a community perspective I can see that a backward compatible solution would be required for earlier version of Excel.
However, Craig has not indicated that backward compatability was an issue, so for simplicity and the closest match to the original request then Adam's solution is the the most elegant answer for Ex'07 +
Just my view, of course.
Had this been last week I would have proposed Adam's Solution as the answer.
The problem with Adam's solution is all computer using the workbook its applied to must be version XL2007 or later... those using XL2003 or earlier cannot make use of it.
Had this been last week I would have proposed Adam's Solution as the answer.
Very nice solution Adam, as it leaves the numeric values intact.