CHOOSE Function

[Paul]

Hi,
I'm using this to return '1st' '2nd' '3rd' etc
=CHOOSE(RANK(H5,$H$5:$H$14),"st","nd","rd","th")
(This is part of a longer formula)
This works OK up to 4th, if the rank is more than 4 it
returns an error.
The ranking goes up to 10th. My question is, is there a
way to get any rank above 4 to return 'th' other than
entering 'th' 7 times in the formula?
TIA
Paul

 

[Jason Morin]

One way:

=CHOOSE(IF(RANK(H5,$H$5:$H$14)>4,4,RANK
(H5,$H$5:$H$14)),"st","nd","rd","th")

HTH
Jason
Atlanta, GA

 

[Avner]

You can try

=IF(RANK(H5,$H$5:$H$14)>3,"th",CHOOSE(RANK(H5,$H$5:$H$14),"st","nd","rd"))


Avne

 

[Alan]

Jason and Avner,
Of course! Woods and trees! Thank you both very much,
Regards,
Paul (Using a friends machine)

 

[Arvi Laanemets]

Hi

=CHOOSE(MIN(RANK(H5,$H$5:$H$14),4),"st","nd","rd","th")


Arvi Laanemets