Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...
MS Office Forum / Excel / Worksheet Functions / October 2006

Tip: Looking for answers? Try searching our database.

Ordinal Numbers

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Epinn - 26 Oct 2006 06:39 GMT
Re:  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 to this Message
Biff - 26 Oct 2006 07:55 GMT
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

Re:  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 to this Message
Epinn - 26 Oct 2006 09:13 GMT
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  

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

Re:  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 to this Message
Bob Phillips - 26 Oct 2006 10:10 GMT
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.

Signature

HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

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
Reply to this Message
Epinn - 26 Oct 2006 10:29 GMT
Don't see Dana on this board.  Wonder if he is nearby.

Bob, thanks for the link and I shall study it.  

Epinn

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.

Signature

HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

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
Reply to this Message
Bob Phillips - 26 Oct 2006 11:54 GMT
> Don't see Dana on this board.  Wonder if he is nearby.

http://tinyurl.com/yz9koo
Reply to this Message
Bob Phillips - 26 Oct 2006 10:08 GMT
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.

Signature

HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

Re:  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 to this Message
Biff - 26 Oct 2006 18:47 GMT
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

>I have a whole discussion page on this at
> http://xldynamic.com/source/xld.RANK.html. You will notice that all of my
[quoted text clipped - 23 lines]
>
> Epinn
Reply to this Message
Bob Phillips - 26 Oct 2006 23:26 GMT
Glad there's something this old dog can still show you young
whipper-snappers <G>

> 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.
>
[quoted text clipped - 3 lines]
> > 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.
Reply to this Message
Epinn - 27 Oct 2006 04:10 GMT
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

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

>I have a whole discussion page on this at
> http://xldynamic.com/source/xld.RANK.html. You will notice that all of my
[quoted text clipped - 23 lines]
>
> Epinn
Reply to this Message
Epinn - 27 Oct 2006 04:22 GMT
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")

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" <biffinpitt@comcast.net> wrote in message news:e6DyBbS#GHA.2180@TK2MSFTNGP05.phx.gbl...
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

>I have a whole discussion page on this at
> http://xldynamic.com/source/xld.RANK.html. You will notice that all of my
[quoted text clipped - 23 lines]
>
> Epinn
Reply to this Message
Biff - 27 Oct 2006 07:44 GMT
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

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" <someone@example.com.NO_SPAM> wrote in message
news:unu2vWX#GHA.3480@TK2MSFTNGP04.phx.gbl...
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

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

>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.
Reply to this Message
Bob Phillips - 27 Oct 2006 08:43 GMT
I would use

=A1&CHOOSE(AND(RIGHT(A1,2)<>{"11","12","13"})*MIN(4,MOD(A1,10))+1,"th","st",
"nd","rd","th")

Signature

HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

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" <someone@example.com.NO_SPAM> wrote in message
news:unu2vWX#GHA.3480@TK2MSFTNGP04.phx.gbl...
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

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

>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.
Reply to this Message
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage
Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.