This week’s NPR Sunday Puzzle was array formula nirvana.
“Take the trees hemlock, myrtle, oak and pine. Rearrange the letters in their names to get four other trees, with one letter left over. What trees are they?”
I made this workbook to solve the puzzle:
At the left is a list of trees whose names meet three criteria:
- The name only contains letters from the four starting trees.
- The name doesn’t contain more of any letter than are in the four starting names combined.
- The name isn’t in the original list.
To solve the puzzle, enter TRUE next to trees to choose them. The list of letters on the right will change as you do. Letters shaded blue are still available and the number remaining is shown. If a letter is shaded orange, you’ve overused it and the “remaining” number is negative. Unshaded letters have been used perfectly.
When the number of trees selected is four and one letter is unused, you’ve solved the puzzle. Go ahead and try it! And, oh yeah, there may be something tricky about the list.
Now for the array formulas, all entered with Ctrl-Shft-Enter. There’s one in the “Used” column:
SUM(LEN(SUBSTITUTE(IF(tblTrees[Use?],tblTrees[Tree],""),[@[Letters To Use]],"")))}
It compares the length of the names chosen against the length of their names after removing the letter, i.e., how many times that letter is used.
To get the list of tree names my family did some brainstorming, which we augmented with a list from the web. It was sliced and diced using Text to Columns and Remove Duplicates from the Excel 2010 Data menu.
Two more array formulas winnow the list to names that meet our three criteria. They’re on the “Good Trees” tab of the workbook. The “Contains Only Valid Letters” formula determines if a name meets the first criteria:
MID($A2,{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15},1)),0)>LEN($A2)}
This formula assumes that the longest name is 15 letters. The CountIf increments through the letters in the word, counting the occurrences of each letter in the list, which will either be one or zero. It uses Match to find the position of the first zero. If that’s greater than the number of letters in the word it passes the test.
The “Contains Less Of Each Letter Than Total” formula checks our second criteria:
(LEN(A2)-LEN(SUBSTITUTE(A2,tblLetters[Letters To Use],"")))),0))}
This also uses the trick of comparing the length of the name against its length with the character removed, to get the count of that letter in the word. It then assumes that the count of that letter overall is greater than or equal to the count in the word, for each letter in the word. If that assumption is FALSE in any letter position the word is no good.
The “Not In Original List” formula checks … you know. The last one combines the first three and if they’re all true the word makes the cut.
I’d love to hear how other people would do these tests. I’m curious too if others are using Excel to solve puzzles, NPR or otherwise. Do you have a good source of lists? I always just google around, with mixed results.
After I had done all the processing I used the workbook to get the answer. I chose three names – elm, hickory and lemon, and had a, e, k, p and t left over. Four of those letters spelled the last tree. It wasn’t on the list but I added it. You could just put it where pecan is now if you want. If you already did, then “teak” a bough. Yew should be proud.
Speaking of tree-related words, one of my favorite words is “pitch.” It’s arboreal, nautical, romantic, and so much more.