• We are pleased to announce that the winner of our Feedback Prize Draw for the Winter 2024-25 session and winning £150 of gift vouchers is Zhao Liang Tay. Congratulations to Zhao Liang. If you fancy winning £150 worth of gift vouchers (from a major UK store) for the Summer 2025 exam sitting for just a few minutes of your time throughout the session, please see our website at https://www.acted.co.uk/further-info.html?pat=feedback#feedback-prize for more information on how you can make sure your name is included in the draw at the end of the session.
  • Please be advised that the SP1, SP5 and SP7 X1 deadline is the 14th July and not the 17th June as first stated. Please accept out apologies for any confusion caused.

Excel Challenge #11 - the Christmas Special

S

Steve Hales

ActEd Tutor
Staff member
Got Excel skills? Let's see.

Here's the Christmas special :)

Here are the usual rules.
  • 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.
  • Conditional formatting is not allowed.
Problem #11

The challenge is to create a single formula to determine whether the contents of cell A1 is an anagram of "CHRISTMAS SPECIAL" or not.

A1
pharmacists slice --> TRUE
ralphs asceticism --> TRUE
spiral schematics --> TRUE
schismatic lapser --> TRUE
merry christmas! --> FALSE

You get the idea!
 
I'll kick off with a not so elegant solution:

=IF(

LEN(SUBSTITUTE(LOWER(SUBSTITUTE(A1," ","")),"a",""))&
LEN(SUBSTITUTE(LOWER(SUBSTITUTE(A1," ","")),"c",""))&
LEN(SUBSTITUTE(LOWER(SUBSTITUTE(A1," ","")),"e",""))&
LEN(SUBSTITUTE(LOWER(SUBSTITUTE(A1," ","")),"h",""))&
LEN(SUBSTITUTE(LOWER(SUBSTITUTE(A1," ","")),"i",""))&
LEN(SUBSTITUTE(LOWER(SUBSTITUTE(A1," ","")),"l",""))&
LEN(SUBSTITUTE(LOWER(SUBSTITUTE(A1," ","")),"m",""))&
LEN(SUBSTITUTE(LOWER(SUBSTITUTE(A1," ","")),"p",""))&
LEN(SUBSTITUTE(LOWER(SUBSTITUTE(A1," ","")),"r",""))&
LEN(SUBSTITUTE(LOWER(SUBSTITUTE(A1," ","")),"s",""))&
LEN(SUBSTITUTE(LOWER(SUBSTITUTE(A1," ","")),"t",""))

= "1414151514151515151315","True","False")

You could remove the lower function form the formula if you knew that your input would never have capital letters in it.

Chris
 
Using the EXACT function instead of IF will shave off a few characters off the formula, though there must be a much shorter solution!

Chris
 
There's always the approach that was offered in Excel Challenge #9:

VLOOKUP(A1,TABLE,2)

where TABLE is the obvious.
Click to expand...
Unfortunately, the obvious TABLE must contain all possible anagrams of "CHRISTMAS SPECIAL", of which there are quite a few ;)
 
Steve Hales said:
There's always the approach that was offered in Excel Challenge #9:


Unfortunately, the obvious TABLE must contain all possible anagrams of "CHRISTMAS SPECIAL", of which there are quite a few ;)
Click to expand...

Or create a user defined function in vba and call it A()
 
=IFERROR(AND(LEN(A1)=17,SUM((LARGE(CODE(MID(UPPER(A1),ROW(A1:A17),1)),ROW(A1:A17))=CODE(MID("TSSSRPMLIIHECCAA ",ROW(A1:A17),1)))*1)=17),FALSE)

Happy Christmas!

Edit: the sorted string should only have a space at the end, but for some reason the web editor is adding another one
 
tiger said:
=IFERROR(AND(LEN(A1)=17,SUM((LARGE(CODE(MID(UPPER(A1),ROW(A1:A17),1)),ROW(A1:A17))=CODE(MID("TSSSRPMLIIHECCAA ",ROW(A1:A17),1)))*1)=17),FALSE)

Happy Christmas!

Edit: the sorted string should only have a space at the end, but for some reason the web editor is adding another one
Click to expand...

I can see your logic here, though cant get the formula to work for the life of me!

...unfortuanately this wouldnt work if you had a 3 (or more) word acronym.

Chris
 
Its an array formula (CSE), so you need to press ctrl-shift-enter instead of enter.

Version for 0 or more spaces allowed
=IFERROR(SUM((LARGE(CODE(MID(UPPER(A1),ROW(OFFSET(A1,,,MAX(16,LEN(A1)),1)),1)),ROW(OFFSET(A1,,,MAX(16,LEN(A1)),1)))=CODE(MID("TSSSRPMLIIHECCAA"&REPT(" ",MAX(LEN(A1)-16,0)),ROW(OFFSET(A1,,,MAX(16,LEN(A1)),1)),1)))*1)=LEN(A1),FALSE)
 
tiger said:
Its an array formula (CSE), so you need to press ctrl-shift-enter instead of enter.

Version for 0 or more spaces allowed
=IFERROR(SUM((LARGE(CODE(MID(UPPER(A1),ROW(OFFSET(A1,,,MAX(16,LEN(A1)),1)),1)),ROW(OFFSET(A1,,,MAX(16,LEN(A1)),1)))=CODE(MID("TSSSRPMLIIHECCAA"&REPT(" ",MAX(LEN(A1)-16,0)),ROW(OFFSET(A1,,,MAX(16,LEN(A1)),1)),1)))*1)=LEN(A1),FALSE)
Click to expand...

Yes - I tried that but nothing. I put it down to a dysfunctional work laptop. It works fine at home.

Nice work on this solution, comfortably less characters than my solution too!
 
Here's an alternative:

=SUM(IFERROR(SIN(CODE(MID(SUBSTITUTE(A1," ",""),ROW(A:A),1))-32),0))=3.25136366359627

But it only works if the following is true:

\(sin(a) + sin(b)=sin(c)+sin(d)\) for \(a,b,c,d \in \mathbb{Z} \Leftrightarrow \{a,b\}=\{c,d\}\).
 
You must log in or register to reply here.
Back
Top