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
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...
B1=0 Code: 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"
That's a great check for those awkward teens - I've be experimenting with Code: ABS(MOD(A1,100)-15)<=5)
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 Code: =MOD(A1,100) & then 0-99 formatting may also not be good. So, I guess Code: =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".
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
Code: 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!
I had a similar one; Code: =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.
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.
Good point. Looks like maz1987 has it with this 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!
I guess you missed my CODE. 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)
Sorry - I'd forgotten about that one So that's a character count of 45 with conditional formatting, and 57 without.
B1=0 