Create ordinal number format in Excel

Anonymous

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

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.

0 comments

58 answers

Andreas Killer 144.1K Reputation points Volunteer Moderator

2014-06-28T15:36:43+00:00

suggested by Andreas, still give "0th" for the non-qualifiers

It seems that I misunderstood something, what does "non-qualifiers" mean? (I'm from Germany, so some expressions are hard to understand.)

Resp. what results do you want to have of the numbers from 0 to 20? Show it to me, then maybe I can give you a formula.

Andreas.
Was this answer helpful?

0 comments No comments
Anonymous

2014-06-28T14:32:06+00:00

I thought this would be the answer - BUT...

The formula by Peter 27th April returns "VALUE", and the suggested amendment here, including the +1, returns the complained of "0th".

So far I have tried the following, which all work to give an Ordinal, but they all, including the last suggested by Andreas, still give "0th" for the non-qualifiers

=AV3&IF(AND(MOD(AV3,100)>=10,MOD(AV3,100)=14),"th",CHOOSE(MOD(AV3,10)+1,"th","st","nd","rd","th","th","th","th","th","th"))

=AV3&" "&MID("thstndrdthstndrdth",MATCH(IF(MOD(AV3,100)>29,MOD(AV3,10)+20,MOD(AV3,100)),{0,1,2,3,4,21,22,23,24},1)*2-1,2)

=AV3&MID("thstndrdth",MIN(9,2*RIGHT(AV3)*(MOD(AV3-11,100)>2)+1),2)

=AV3&IF(OR(RIGHT(AV3,2)="11",RIGHT(AV3,2)="12",RIGHT(AV3,2)="13"),"th",CHOOSE(RIGHT(AV3,1)+1,"th","st","nd","rd","th","th","th","th","th","th","th"))

Are there any other theories, please?

Daleman9
Was this answer helpful?

0 comments No comments
Andreas Killer 144.1K Reputation points Volunteer Moderator

2014-06-27T10:11:23+00:00

I have tried the above formula, and others on this site, but always have "0th" for the ones that don't qualify!

Use this one instead:

=J4&IF(OR(RIGHT(J4,2)="11",RIGHT(J4,2)="12",RIGHT(J4,2)="13"),"th",CHOOSE(RIGHT(J4,1)+1,"th","st","nd","rd","th","th","th","th","th","th","th"))

Andreas.
Was this answer helpful?

0 comments No comments
Anonymous

2014-06-27T09:31:54+00:00

Thank you for this. I have a list of 100 people that I want to choose the top 10 and record the result as ordinal. I have tried the above formula, and others on this site, but always have "0th" for the ones that don't qualify! Can I amend the formula so that it shows blank for the other 99?
Was this answer helpful?

0 comments No comments
Paul Edstein 82,871 Reputation points Volunteer Moderator

2014-04-27T21:09:53+00:00

While that's OK for producing an ordinal version of a number as text in a different cell, it doesn't meet the OP's requirements, which were given as:

what I would like to do is have the number display as an ordinal number(e.g., "1st", "2nd", "3rd", etc.) while still retaining its numeric value (1, 2, 3, etc.).
Was this answer helpful?

0 comments No comments