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: Unfortunately, the obvious TABLE must contain all possible anagrams of "CHRISTMAS SPECIAL", of which there are quite a few
=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
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)
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\}\).
