Excel Challenge #3

[Steve Hales]

You've got Excel skills? Let's see.

These challenges are working out really well, so here goes for number three. The rules are even simpler than before:

• Create a single Excel formula to solve the stated problem.
• The shortest formula wins (when calculating the length of the formula there's no need to include the leading "=", or the curly braces that Excel uses to represent array formulae).
• The formula's number format can be set to whatever you want.

Problem #3

Assume that cell A1 contains a positive integer. Create a formula in B1 to return the English ordinal indicator associated with the value in A1.

For example

A1 --> B1
1 --> "st"
2 --> "nd"
3 --> "rd"
4 --> "th"
11 --> "th"
21 --> "st"

I suspect that there are quite a few different ways of achieving this - so it'll be interesting to see what we can come up with

 

[Oxymoron]

The obligatory MATLAB answer.
a = iptnum2ordinal(20); b = a(end-1:end)

 

[Bodhisattva]

I think this works:

=IF(OR(MOD(A1-1,10)>2,MOD(INT(A1/10),10)=1),"th",CHOOSE(MOD(A1,10),"st","nd","rd"))

I really should be revising instead...

 

[Hemant Rupani]

B1=0

Formatting
=AND(MOD(A1,100)>10,MOD(A1,100)<14) to "th"
=MOD(A1,10)=3 to "rd"
=MOD(A1,10)=2 to "nd"
=MOD(A1,10)=1 to "st"
=OR(MOD(A1,10)>3,MOD(A1,10)=0) to "th"

 

[Steve Hales]

MATLAB

Again? Really?

 

[Steve Hales]

MOD(INT(A1/10),10)=1

That's a great check for those awkward teens - I've be experimenting with

ABS(MOD(A1,100)-15)<=5)

 

[Steve Hales]

Formatting
=AND(MOD(A1,100)>10,MOD(A1,100)<14) to "th"
=MOD(A1,10)=3 to "rd"
=MOD(A1,10)=2 to "nd"
=MOD(A1,10)=1 to "st"
=OR(MOD(A1,10)>3,MOD(A1,10)=0) to "th"

Not really a "single Excel formula", but it is clever. Using this approach it'll be possible to complete all of the Excel challenges with only "=0" and some fancy conditional formatting

 

[Hemant Rupani]

Not really a "single Excel formula", but it is clever. Using this approach it'll be possible to complete all of the Excel challenges with only "=0" and some fancy conditional formatting

ohh yes !
I think using

=MOD(A1,100)

& then 0-99 formatting may also not be good.

So,
I guess

=IF(MOD(A1,10)=MOD(A1,100)-10,"th",MOD(A1,10))

is acceptable
formatting 0 to "th",1 to "st",2 to"nd",3 to "rd".

 

[Oxymoron]

Again? Really?

Quoting Martin Luther King:
I have a dream that my four little children will one day live in a forum where they will not be judged by the colour of their software but by the power of its syntax.

There is a revolution spreading from the deepest darkest pit of the online forums. A revolution, Steve, that will devour people such as yourself and those who conform and post excel formulas in this thread. Weep for the world you once knew, for it is mere crumbs upon the sill of despair soon to be swept away by the gusts of change

 

[sahildh]

Quoting Martin Luther King:
I have a dream that my four little children will one day live in a forum where they will not be judged by the colour of their software but by the power of its syntax.

There is a revolution spreading from the deepest darkest pit of the online forums. A revolution, Steve, that will devour people such as yourself and those you conform and post excel formulas in this thread. Weep for the world you once knew, for it is mere crumbs upon the sill of despair soon to be swept away by the gusts of change

 

[maz1987]

IFERROR(CHOOSE(MOD(A1,10)*(MOD(A1-10,100)>4), -1, 0, 1),"th")

(58 characters)

The MOD(....) part gives:
1 if "st"
2 if "nd"
3 if "rd"
0 if "th" (where the (MOD(A2-10,100)>4) returns 0 for numbers 11, 12 and 13

CHOOSE(...) converts these to {-1, 0, 1, #VALUE!} respectively (where #VALUE! is returned if it chooses a value placed 4th or higher in the list i.e. doesn't exist).

IFERROR(...) converts the #VALUE! to "th".

Changing the formatting to "rd";"st";"nd" sorts out the -1, 0 and 1

Hadn't ever come across the CHOOSE() function so good job Bodhisattva!

 

[Steve Hales]

There is a revolution spreading from the deepest darkest pit of the online forums. A revolution, Steve, that will devour people such as yourself and those who conform and post excel formulas in this thread. Weep for the world you once knew, for it is mere crumbs upon the sill of despair soon to be swept away by the gusts of change

OK, I'll be ready for it, but in the meantime - use Excel

 

[Steve Hales]

Changing the formatting to "rd";"st";"nd" sorts out the -1, 0 and 1

I had a similar one;

=IF(OR(MOD(A1,10)>3,ABS(MOD(A1,100)-15)<=5),"th",MOD(A1,10)-2)

with number format "rd";"st";"nd", but the character count is 61.

 

[Steve Hales]

I think that exams have got in the way this month - hope they're going well!

Watch out for Excel Challenge #4 - launching Friday 15th May 2015.

 

[Hemant Rupani]

I think that exams have got in the way this month - hope they're going well!

Watch out for Excel Challenge #4 - launching Friday 15th May 2015.

Ohhh Yes! But you didn't declare winner?

 

[Steve Hales]

Ohhh Yes! But you didn't declare winner?

Good point. Looks like maz1987 has it with this

IFERROR(CHOOSE(MOD(A1,10)*(MOD(A1-10,100)>4),-1,0,1),"th")

The character count is 58. The CHOOSE function can survive without the "0" so "-1,0,1" becomes "-1,,1" and the count goes to 57.

Well done maz1987!

 

[Hemant Rupani]

I guess you missed my CODE.

=IF(MOD(A1,10)=MOD(A1,100)-10,"th",MOD(A1,10))

with 45 count
formatting 0 to "th",1 to "st",2 to"nd",3 to "rd".(and these are pure formula's number format)

 

[Steve Hales]

I guess you missed my CODE.

Sorry - I'd forgotten about that one

So that's a character count of 45 with conditional formatting, and 57 without.

 

[maz1987]

Definitely had to prioritise exams over Excel challenge!