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

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

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

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

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

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

Ordinal Numbers
 

"Epinn" wrote in message
...


Don't see Dana on this board. Wonder if he is nearby.

http://tinyurl.com/yz9koo

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

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)

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

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

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

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