Adding st, nd, rd, th to ranked values

[masud8956]

Hi everyone!

I am using the following formula to rank 20 rows of entries at S5:S24.

=IF(ISNUMBER(R5),1+COUNTIF($R$5:$R$24,">"&R5),"")

What modification do I need to get the texts st, nd, rd, th as ordinal suffix?

Regards

 

[Peter_SSs]

As an additional note, if the underlying cardinal values are wanting to be retained, here is an option to add the ordinal suffixes by Conditional Formatting.
... But without the superscript

 

[Rick Rothstein]

Teamwork... you gotta love it. :wink:

 

[FormR]

... But without the superscript

I think you can also use the unicode superscript characters in the custom number format

 

[Peter_SSs]

I think you can also use the unicode superscript characters in the custom number format

You can indeed, thanks -->

 

[BiocideJ]

One word of caution with the prettier formula (although it may not matter if your range stays at <= 20 cells)...
it will convert 21, 22, 23 to 21th, 22th, 23th.


Loved seeing all of the great ideas in this thread though.

 

[BiocideJ]

I retract my previous statement (about the word of caution).

It will work fine as long as you adjust all of the ranges...unlike what I first attempted.