Ordinal Numbers

E

Epinn

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
 
B

Biff

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
 
E

Epinn

I am glad that I have sense of perfection and I posted, otherwise I wouldn't have found out your wonderful trick.

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
 
B

Bob Phillips

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)

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
 
B

Bob Phillips

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)

I am glad that I have sense of perfection and I posted, otherwise I wouldn't
have found out your wonderful trick.

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
 
E

Epinn

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.

--
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.

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
 
B

Biff

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
 
B

Bob Phillips

Glad there's something this old dog can still show you young
whipper-snappers <G>
 
E

Epinn

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
 
E

Epinn

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

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
 
B

Biff

Seems we have a bug, eh!
Just tag on one more zero, instead of A1, 10 make it A1, 100.
Click to expand...

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

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
 
B

Bob Phillips

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)

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

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How to custom format a date 5
customize date 11
Date abbreviations / extensions 6
Create dates for year, allow for leap year 8
separate numbers out of string 3
Calculate the total overlapping time of multiple tasks, excluding non-working times 1
Help with a formula (calculating totals based on individual percentages 7
Multiple table lookup 13

Top