CHOOSE Function
 

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

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

-----Original Message-----
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
.

You can try

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


Avner


--
Avner
------------------------------------------------------------------------
Avner's Profile: http://www.excelforum.com/member.php...fo&userid=5078
View this thread: http://www.excelforum.com/showthread...hreadid=274515

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

You can try

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


Avner


--
Avner
------------------------------------------------------------------------
Avner's Profile:
http://www.excelforum.com/member.php...fo&userid=5078
View this thread: http://www.excelforum.com/showthread...hreadid=274515

Hi

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


Arvi Laanemets


"Paul" wrote in message
...
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