1st, 2nd, 3rd etc.

**creationracing** · 02-09-2008, 04:32 PM

Hi all,

I've developed a sheet which works works out a competitors score in a competition, and their position in the contest (using the RANK function).

However this only displays their position as an integer number, i.e. "1" for the winner, "2" for the runner-up, etc. Is there any way I can format the range so their positions appear as 1st, 2nd, 3rd, 4th... etc?

I could use a lookup table to get this effect but I'm sure there must be another way to achieve this?

Thanks in advance!

Roy K.

**VBA Noob** · 02-09-2008, 04:37 PM

Maybe you could add the choose function to your formula

e.g If A1 = 1 returns 1st etc

=A1&CHOOSE(A1,"st","nd","rd","th")

VBA Noob

**oldchippy** · 02-09-2008, 04:42 PM

Or may this if its less than 21 in total

=IF(A1>4,A1&"th",A1&CHOOSE(A1,"st","nd","rd","th"))

**daddylonglegs** · 02-09-2008, 04:52 PM

For any number in A1

=A1&IF(OR(MOD(A1,100)={11,12,13}),"th",CHOOSE(MIN(5,RIGHT(A1)+1),"th","st","nd","rd","th"))

Obviously you can do this with one formula if you substitute your RANK function for each of the A1s in the above.....

edit....for up to 20, you could use

=A1&LOOKUP(A1,{0,1,2,3,4;"th","st","nd","rd","th"})

1st, 2nd, 3rd etc.

LinkBack

Thread Tools

Rate This Thread

Display

1st, 2nd, 3rd etc.

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions