Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 . |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Averaging function | Excel Discussion (Misc queries) | |||
Function in XL or in VBA for XL that pulls numeric digits from a t | Excel Discussion (Misc queries) | |||
I cant use englisch function names in a swedich version of excel | Excel Discussion (Misc queries) | |||
need to save values from a function before it changes | Excel Worksheet Functions | |||
SUMIF(AND) FUNCTION | Excel Worksheet Functions |