| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Ordinal Numbers
http://www.cpearson.com/excel/ordinal.htm
=A1&IF(AND(MOD(A1,100)=10,MOD(A1,100)<=14),"th", CHOOSE(MOD(A1,10)+1,"th","st","nd","rd","th","th", "th","th","th","th")) I like the fact that this formula uses CHOOSE(). But I have a problem with =10 and <=14. I am more inclined to use =11 and <=13. So I change the formula to the following. =A1&IF(AND(MOD(A1,100)=11,MOD(A1,100)<=13),"th", CHOOSE(MOD(A1,10)+1,"th","st","nd","rd","th","th", "th","th","th","th")) It works fine and makes me feel *logical*. Does anyone see any reason why I shouldn't change to 11 and 13 respectively? Thanks. Epinn |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Ordinal Numbers
Here's what I typically use:
=A1&IF(OR(MOD(A1,100)={11,12,13}),"th",LOOKUP(--RIGHT(A1),{0,"th";1,"st";2,"nd";3,"rd";4,"th"})) It's a few keystrokes shorter. Of course, it'd be a little longer with an error trap. (A1 being empty causes #VALUE!) Biff "Epinn" wrote in message ... http://www.cpearson.com/excel/ordinal.htm =A1&IF(AND(MOD(A1,100)=10,MOD(A1,100)<=14),"th", CHOOSE(MOD(A1,10)+1,"th","st","nd","rd","th","th", "th","th","th","th")) I like the fact that this formula uses CHOOSE(). But I have a problem with =10 and <=14. I am more inclined to use =11 and <=13. So I change the formula to the following. =A1&IF(AND(MOD(A1,100)=11,MOD(A1,100)<=13),"th", CHOOSE(MOD(A1,10)+1,"th","st","nd","rd","th","th", "th","th","th","th")) It works fine and makes me feel *logical*. Does anyone see any reason why I shouldn't change to 11 and 13 respectively? Thanks. Epinn |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Ordinal Numbers
I am glad that I have sense of perfection and I posted, otherwise I wouldn't have found out your wonderful trick.
It's a few keystrokes shorter. Probably faster too, but we won't be able to tell the difference. ;) What is smaller than nano-second? Off topic: You were wondering the gender of Dana DeLouis? According to Dave P., the answer is male. Is he an MVP? Thanks for your help. Epinn "Biff" wrote in message ... Here's what I typically use: =A1&IF(OR(MOD(A1,100)={11,12,13}),"th",LOOKUP(--RIGHT(A1),{0,"th";1,"st";2,"nd";3,"rd";4,"th"})) Of course, it'd be a little longer with an error trap. (A1 being empty causes #VALUE!) Biff "Epinn" wrote in message ... http://www.cpearson.com/excel/ordinal.htm =A1&IF(AND(MOD(A1,100)=10,MOD(A1,100)<=14),"th", CHOOSE(MOD(A1,10)+1,"th","st","nd","rd","th","th", "th","th","th","th")) I like the fact that this formula uses CHOOSE(). But I have a problem with =10 and <=14. I am more inclined to use =11 and <=13. So I change the formula to the following. =A1&IF(AND(MOD(A1,100)=11,MOD(A1,100)<=13),"th", CHOOSE(MOD(A1,10)+1,"th","st","nd","rd","th","th", "th","th","th","th")) It works fine and makes me feel *logical*. Does anyone see any reason why I shouldn't change to 11 and 13 respectively? Thanks. Epinn |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Ordinal Numbers
I have a whole discussion page on this at
http://xldynamic.com/source/xld.RANK.html. You will notice that all of my formulae test 11,12,13, so my guess would be no problem at all. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Epinn" wrote in message ... http://www.cpearson.com/excel/ordinal.htm =A1&IF(AND(MOD(A1,100)=10,MOD(A1,100)<=14),"th", CHOOSE(MOD(A1,10)+1,"th","st","nd","rd","th","th", "th","th","th","th")) I like the fact that this formula uses CHOOSE(). But I have a problem with =10 and <=14. I am more inclined to use =11 and <=13. So I change the formula to the following. =A1&IF(AND(MOD(A1,100)=11,MOD(A1,100)<=13),"th", CHOOSE(MOD(A1,10)+1,"th","st","nd","rd","th","th", "th","th","th","th")) It works fine and makes me feel *logical*. Does anyone see any reason why I shouldn't change to 11 and 13 respectively? Thanks. Epinn |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Ordinal Numbers
Dana is indeed a bloke, a very nice bloke, and a very smart guy. He always
seems to have an interesting, somewhat skewed, perspective on a problem, which provides for interesting solutions. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Epinn" wrote in message ... I am glad that I have sense of perfection and I posted, otherwise I wouldn't have found out your wonderful trick. It's a few keystrokes shorter. Probably faster too, but we won't be able to tell the difference. ;) What is smaller than nano-second? Off topic: You were wondering the gender of Dana DeLouis? According to Dave P., the answer is male. Is he an MVP? Thanks for your help. Epinn |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Ordinal Numbers
Don't see Dana on this board. Wonder if he is nearby.
Bob, thanks for the link and I shall study it. Epinn "Bob Phillips" wrote in message ... Dana is indeed a bloke, a very nice bloke, and a very smart guy. He always seems to have an interesting, somewhat skewed, perspective on a problem, which provides for interesting solutions. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Epinn" wrote in message ... I am glad that I have sense of perfection and I posted, otherwise I wouldn't have found out your wonderful trick. It's a few keystrokes shorter. Probably faster too, but we won't be able to tell the difference. ;) What is smaller than nano-second? Off topic: You were wondering the gender of Dana DeLouis? According to Dave P., the answer is male. Is he an MVP? Thanks for your help. Epinn |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Ordinal Numbers
"Epinn" wrote in message ... Don't see Dana on this board. Wonder if he is nearby. http://tinyurl.com/yz9koo |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Ordinal Numbers
I've never read that page before. Excellent info!
=A1&CHOOSE(AND(A1<{11,12,13})*MIN(4,MOD(A1,10))+1 ,"th","st","nd","rd","th") I like that. It's a few keystrokes shorter than what I use. Biff "Bob Phillips" wrote in message ... I have a whole discussion page on this at http://xldynamic.com/source/xld.RANK.html. You will notice that all of my formulae test 11,12,13, so my guess would be no problem at all. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Epinn" wrote in message ... http://www.cpearson.com/excel/ordinal.htm =A1&IF(AND(MOD(A1,100)=10,MOD(A1,100)<=14),"th", CHOOSE(MOD(A1,10)+1,"th","st","nd","rd","th","th", "th","th","th","th")) I like the fact that this formula uses CHOOSE(). But I have a problem with =10 and <=14. I am more inclined to use =11 and <=13. So I change the formula to the following. =A1&IF(AND(MOD(A1,100)=11,MOD(A1,100)<=13),"th", CHOOSE(MOD(A1,10)+1,"th","st","nd","rd","th","th", "th","th","th","th")) It works fine and makes me feel *logical*. Does anyone see any reason why I shouldn't change to 11 and 13 respectively? Thanks. Epinn |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Ordinal Numbers
Glad there's something this old dog can still show you young
whipper-snappers <G "Biff" wrote in message ... I've never read that page before. Excellent info! =A1&CHOOSE(AND(A1<{11,12,13})*MIN(4,MOD(A1,10))+1 ,"th","st","nd","rd","th") I like that. It's a few keystrokes shorter than what I use. Biff "Bob Phillips" wrote in message ... I have a whole discussion page on this at http://xldynamic.com/source/xld.RANK.html. You will notice that all of my formulae test 11,12,13, so my guess would be no problem at all. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Ordinal Numbers
Sorry, I need help. I don't know how to modify the formula to make it work for all numbers.
I have different results from the following two formulae respectively. =A1&CHOOSE(AND(A1<{11,12,13})*MIN(4,MOD(A1,10))+1 ,"th","st","nd","rd","th") gives me 111st, 112nd, 113rd etc. =A1&IF(OR(MOD(A1,100)={11,12,13}),"th",LOOKUP(--RIGHT(A1),{0,"th";1,"st";2,"nd";3,"rd";4,"th"})) gives me 111th, 112th, 113th etc. Both formulae will be perfect for days of the week as max. is 31st. Appreciate advice. Epinn "Biff" wrote in message ... I've never read that page before. Excellent info! =A1&CHOOSE(AND(A1<{11,12,13})*MIN(4,MOD(A1,10))+1 ,"th","st","nd","rd","th") I like that. It's a few keystrokes shorter than what I use. Biff "Bob Phillips" wrote in message ... I have a whole discussion page on this at http://xldynamic.com/source/xld.RANK.html. You will notice that all of my formulae test 11,12,13, so my guess would be no problem at all. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Epinn" wrote in message ... http://www.cpearson.com/excel/ordinal.htm =A1&IF(AND(MOD(A1,100)=10,MOD(A1,100)<=14),"th", CHOOSE(MOD(A1,10)+1,"th","st","nd","rd","th","th", "th","th","th","th")) I like the fact that this formula uses CHOOSE(). But I have a problem with =10 and <=14. I am more inclined to use =11 and <=13. So I change the formula to the following. =A1&IF(AND(MOD(A1,100)=11,MOD(A1,100)<=13),"th", CHOOSE(MOD(A1,10)+1,"th","st","nd","rd","th","th", "th","th","th","th")) It works fine and makes me feel *logical*. Does anyone see any reason why I shouldn't change to 11 and 13 respectively? Thanks. Epinn |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Ordinal Numbers
Okay, I have figured it out. Just tag on one more zero, instead of A1, 10 make it A1, 100.
=A1&CHOOSE(AND(A1<{11,12,13})*MIN(4,MOD(A1,100))+ 1,"th","st","nd","rd","th") Epinn "Epinn" wrote in message ... Sorry, I need help. I don't know how to modify the formula to make it work for all numbers. I have different results from the following two formulae respectively. =A1&CHOOSE(AND(A1<{11,12,13})*MIN(4,MOD(A1,10))+1 ,"th","st","nd","rd","th") gives me 111st, 112nd, 113rd etc. =A1&IF(OR(MOD(A1,100)={11,12,13}),"th",LOOKUP(--RIGHT(A1),{0,"th";1,"st";2,"nd";3,"rd";4,"th"})) gives me 111th, 112th, 113th etc. Both formulae will be perfect for days of the week as max. is 31st. Appreciate advice. Epinn "Biff" wrote in message ... I've never read that page before. Excellent info! =A1&CHOOSE(AND(A1<{11,12,13})*MIN(4,MOD(A1,10))+1 ,"th","st","nd","rd","th") I like that. It's a few keystrokes shorter than what I use. Biff "Bob Phillips" wrote in message ... I have a whole discussion page on this at http://xldynamic.com/source/xld.RANK.html. You will notice that all of my formulae test 11,12,13, so my guess would be no problem at all. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Epinn" wrote in message ... http://www.cpearson.com/excel/ordinal.htm =A1&IF(AND(MOD(A1,100)=10,MOD(A1,100)<=14),"th", CHOOSE(MOD(A1,10)+1,"th","st","nd","rd","th","th", "th","th","th","th")) I like the fact that this formula uses CHOOSE(). But I have a problem with =10 and <=14. I am more inclined to use =11 and <=13. So I change the formula to the following. =A1&IF(AND(MOD(A1,100)=11,MOD(A1,100)<=13),"th", CHOOSE(MOD(A1,10)+1,"th","st","nd","rd","th","th", "th","th","th","th")) It works fine and makes me feel *logical*. Does anyone see any reason why I shouldn't change to 11 and 13 respectively? Thanks. Epinn |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Ordinal Numbers
Seems we have a bug, eh!
Just tag on one more zero, instead of A1, 10 make it A1, 100. Still buggy. Try 732 Biff "Epinn" wrote in message ... Okay, I have figured it out. Just tag on one more zero, instead of A1, 10 make it A1, 100. =A1&CHOOSE(AND(A1<{11,12,13})*MIN(4,MOD(A1,100))+ 1,"th","st","nd","rd","th") Epinn "Epinn" wrote in message ... Sorry, I need help. I don't know how to modify the formula to make it work for all numbers. I have different results from the following two formulae respectively. =A1&CHOOSE(AND(A1<{11,12,13})*MIN(4,MOD(A1,10))+1 ,"th","st","nd","rd","th") gives me 111st, 112nd, 113rd etc. =A1&IF(OR(MOD(A1,100)={11,12,13}),"th",LOOKUP(--RIGHT(A1),{0,"th";1,"st";2,"nd";3,"rd";4,"th"})) gives me 111th, 112th, 113th etc. Both formulae will be perfect for days of the week as max. is 31st. Appreciate advice. Epinn "Biff" wrote in message ... I've never read that page before. Excellent info! =A1&CHOOSE(AND(A1<{11,12,13})*MIN(4,MOD(A1,10))+1 ,"th","st","nd","rd","th") I like that. It's a few keystrokes shorter than what I use. Biff "Bob Phillips" wrote in message ... I have a whole discussion page on this at http://xldynamic.com/source/xld.RANK.html. You will notice that all of my formulae test 11,12,13, so my guess would be no problem at all. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Epinn" wrote in message ... http://www.cpearson.com/excel/ordinal.htm =A1&IF(AND(MOD(A1,100)=10,MOD(A1,100)<=14),"th", CHOOSE(MOD(A1,10)+1,"th","st","nd","rd","th","th", "th","th","th","th")) I like the fact that this formula uses CHOOSE(). But I have a problem with =10 and <=14. I am more inclined to use =11 and <=13. So I change the formula to the following. =A1&IF(AND(MOD(A1,100)=11,MOD(A1,100)<=13),"th", CHOOSE(MOD(A1,10)+1,"th","st","nd","rd","th","th", "th","th","th","th")) It works fine and makes me feel *logical*. Does anyone see any reason why I shouldn't change to 11 and 13 respectively? Thanks. Epinn |
|
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Ordinal Numbers
I would use
=A1&CHOOSE(AND(RIGHT(A1,2)<{"11","12","13"})*MIN( 4,MOD(A1,10))+1,"th","st", "nd","rd","th") -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Epinn" wrote in message ... Okay, I have figured it out. Just tag on one more zero, instead of A1, 10 make it A1, 100. =A1&CHOOSE(AND(A1<{11,12,13})*MIN(4,MOD(A1,100))+ 1,"th","st","nd","rd","th" ) Epinn "Epinn" wrote in message ... Sorry, I need help. I don't know how to modify the formula to make it work for all numbers. I have different results from the following two formulae respectively. =A1&CHOOSE(AND(A1<{11,12,13})*MIN(4,MOD(A1,10))+1 ,"th","st","nd","rd","th") gives me 111st, 112nd, 113rd etc. =A1&IF(OR(MOD(A1,100)={11,12,13}),"th",LOOKUP(--RIGHT(A1),{0,"th";1,"st";2," nd";3,"rd";4,"th"})) gives me 111th, 112th, 113th etc. Both formulae will be perfect for days of the week as max. is 31st. Appreciate advice. Epinn "Biff" wrote in message ... I've never read that page before. Excellent info! =A1&CHOOSE(AND(A1<{11,12,13})*MIN(4,MOD(A1,10))+1 ,"th","st","nd","rd","th") I like that. It's a few keystrokes shorter than what I use. Biff "Bob Phillips" wrote in message ... I have a whole discussion page on this at http://xldynamic.com/source/xld.RANK.html. You will notice that all of my formulae test 11,12,13, so my guess would be no problem at all. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Epinn" wrote in message ... http://www.cpearson.com/excel/ordinal.htm =A1&IF(AND(MOD(A1,100)=10,MOD(A1,100)<=14),"th", CHOOSE(MOD(A1,10)+1,"th","st","nd","rd","th","th", "th","th","th","th")) I like the fact that this formula uses CHOOSE(). But I have a problem with =10 and <=14. I am more inclined to use =11 and <=13. So I change the formula to the following. =A1&IF(AND(MOD(A1,100)=11,MOD(A1,100)<=13),"th", CHOOSE(MOD(A1,10)+1,"th","st","nd","rd","th","th", "th","th","th","th")) It works fine and makes me feel *logical*. Does anyone see any reason why I shouldn't change to 11 and 13 respectively? Thanks. Epinn |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Replace Old Part Numbers with New Part Numbers in a Macro. | Excel Discussion (Misc queries) | |||
| Format cells to display ordinal numbers ex 21st | Excel Worksheet Functions | |||
| conditional formatting | Excel Worksheet Functions | |||
| How to generate sets of random numbers without having duplicates | Excel Worksheet Functions | |||
| Why are 1/2 my numbers imported as text and the rest as numbers? | Excel Discussion (Misc queries) | |||
Linear Mode
