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