Hi,

Can you help make this formula shorter/better/more efficient - I need to go
up to
90th

=IF(A1="","",LOOKUP(A1,{1,99,183,267,351,435,519,6 03,687,771,855,939,1023,1107,1191,1275,1359,1443,1 527,1611,1695,1779,1863,1947,2031,2115,2199,2283,2 367,2451,2535,2619,2703,2787,2871,2955,3039,3123,3 207,3291,3375,3459,3543,3627,3711,3795,3879,3963,4 047,4131,4215,4299,4383,4467,4551,4635,4719,4803,4 887,4971,5055,5139,5223,5307,5391,5475,5559,5643,5 727,5811,5895,5979,6063,6147,6231,6315,6399,6483,6 567,6651,6735,6819,6903,6987},{"1st","2nd","3rd"," 4th","5th","6th","7th","8th","9th","10th","11th"," 12th","13th","14th","15th","16th","17th","18th","1 9th","20","21st","22nd","23rd","24th","25th","26th ","27th","28th","29th","30th","31st","32nd","33rd" ,"34th","35th","36th","37th","38th","39th","40th", "41st","42nd","43rd","44th","45th","46th","47th"," 48th","49th","50th","51st","52nd","53rd","54th","5 5th","56th","57th","58th","59th","60th","61st","62 nd","63rd","64th","65th","66th","67th","68th","69t h","70th","71st","72nd","73rd","74th","75th","76th ","77th","78th","79th","Older"}))

A better way (both for formula length, and ease of future edits) would be to
create a table somewhere (say A2:B100) and in first column, input 1, 99 and
then in A4 (=A3+84) and drag down, and in 2nd column, your ranks (note that
XL can extrapolate if you input 1st, 2nd, and then drag down).
Then your formula can simply be:
=IF(A1="","",LOOKUP(A1,A2:B100))
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Ward" wrote:

Hi,

Can you help make this formula shorter/better/more efficient - I need to go
up to
90th

=IF(A1="","",LOOKUP(A1,{1,99,183,267,351,435,519,6 03,687,771,855,939,1023,1107,1191,1275,1359,1443,1 527,1611,1695,1779,1863,1947,2031,2115,2199,2283,2 367,2451,2535,2619,2703,2787,2871,2955,3039,3123,3 207,3291,3375,3459,3543,3627,3711,3795,3879,3963,4 047,4131,4215,4299,4383,4467,4551,4635,4719,4803,4 887,4971,5055,5139,5223,5307,5391,5475,5559,5643,5 727,5811,5895,5979,6063,6147,6231,6315,6399,6483,6 567,6651,6735,6819,6903,6987},{"1st","2nd","3rd"," 4th","5th","6th","7th","8th","9th","10th","11th"," 12th","13th","14th","15th","16th","17th","18th","1 9th","20","21st","22nd","23rd","24th","25th","26th ","27th","28th","29th","30th","31st","32nd","33rd" ,"34th","35th","36th","37th","38th","39th","40th", "41st","42nd","43rd","44th","45th","46th","47th"," 48th","49th","50th","51st","52nd","53rd","54th","5 5th","56th","57th","58th","59th","60th","61st","62 nd","63rd","64th","65th","66th","67th","68th","69t h","70th","71st","72nd","73rd","74th","75th","76th ","77th","78th","79th","Older"}))

Hello,

If Iunderstand you correctly:
=IF(A1<99,"1st",IF(A17574,"Older",ROUNDUP((A1-14)/84,0)&MID
("thstndrdth",MIN(9,2*RIGHT(ROUNDUP((A1-14)/84,0))*(MOD(ROUNDUP
((A1-14)/84,0)-11,100)2)+1),2)))

And if this is correct, the credit for the ordinal part belongs to
Rick Rothstein:
http://groups.google.de/group/micros...70e52 ba21036

Regards,
Bernd

Ward wrote:
Hi,

Can you help make this formula shorter/better/more efficient - I need to go
up to
90th

=IF(A1="","",LOOKUP(A1,{1,99,183,267,351,435,519,6 03,687,771,855,939,1023,1107,1191,1275,1359,1443,1 527,1611,1695,1779,1863,1947,2031,2115,2199,2283,2 367,2451,2535,2619,2703,2787,2871,2955,3039,3123,3 207,3291,3375,3459,3543,3627,3711,3795,3879,3963,4 047,4131,4215,4299,4383,4467,4551,4635,4719,4803,4 887,4971,5055,5139,5223,5307,5391,5475,5559,5643,5 727,5811,5895,5979,6063,6147,6231,6315,6399,6483,6 567,6651,6735,6819,6903,6987},{"1st","2nd","3rd"," 4th","5th","6th","7th","8th","9th","10th","11th"," 12th","13th","14th","15th","16th","17th","18th","1 9th","20","21st","22nd","23rd","24th","25th","26th ","27th","28th","29th","30th","31st","32nd","33rd" ,"34th","35th","36th","37th","38th","39th","40th", "41st","42nd","43rd","44th","45th","46th","47th"," 48th","49th","50th","51st","52nd","53rd","54th","5 5th","56th","57th","58th","59th","60th","61st","62 nd","63rd","64th","65th","66th","67th","68th","69t h","70th","71st","72nd","73rd","74th","75th","76th ","77th","78th","79th","Older"}))



http://www.contextures.com/xlFunctions02.html

Glenn wrote:
Ward wrote:
Hi,

Can you help make this formula shorter/better/more efficient - I need
to go up to 90th
=IF(A1="","",LOOKUP(A1,{1,99,183,267,351,435,...69 03,6987},{"1st","2nd","3rd","4th","5th","6th",..." 79th","Older"}))




http://www.contextures.com/xlFunctions02.html


Maybe this:

=IF(A1="","",ROUND((A1-99)/84,0)+2&MID("thstndrdth",
MIN(9,2*RIGHT(ROUND((A1-99)/84,0)+2)*
(MOD(ROUND((A1-99)/84,0)+2-11,100)2)+1),2))

Glenn wrote:
Glenn wrote:
Ward wrote:
Hi,

Can you help make this formula shorter/better/more efficient - I need
to go up to 90th
=IF(A1="","",LOOKUP(A1,{1,99,183,267,351,435,...69 03,6987},{"1st","2nd","3rd","4th","5th","6th",..." 79th","Older"}))




http://www.contextures.com/xlFunctions02.html


Maybe this:

=IF(A1="","",ROUND((A1-99)/84,0)+2&MID("thstndrdth",
MIN(9,2*RIGHT(ROUND((A1-99)/84,0)+2)*
(MOD(ROUND((A1-99)/84,0)+2-11,100)2)+1),2))


Corrected:

=IF(A1="","",IF(A1<99,"1st",TRUNC((A1-99)/84,0)+2&MID("thstndrdth",
MIN(9,2*RIGHT(TRUNC((A1-99)/84,0)+2)*
(MOD(TRUNC((A1-99)/84,0)+2-11,100)2)+1),2)))