Category Archives: Fun

Every time I hear Will Shortz say “name a country of the world” my heart beats a little faster, cause I know there’s probably an array-formula post in it. This week was no exception:

“Name an article of clothing that contains three consecutive letters of the alphabet consecutively in the word. For example, “canopy” contains the consecutive letters N-O-P. This article of clothing is often worn in a country whose name also contains three consecutive letters of the alphabet together. What is the clothing article, and what is the country?”

This spreadsheet is live. Double-clicking in a cell in the 2nd column shows the formula. You can then drag the lower-right fill handle to see the whole gnarly thing.

Countries are a lot more list-friendly, so I solved that part first. There are two types of solutions above: an array formula in the 2nd column, and a conditional formatting solution in the 3rd to 40-somethingth column. The 2nd solution is the way I’d do it for serious work, but the array formula is more fun:

This formula says to compare every letter in the country name to the next two letters. If the numeric code for a letter is one less than the letter after it, and two less than the letter after that, return those three letters. The reason the 2nd column is mostly blank is because surprisingly few country names have three consecutive letters. (Or my formula sucks.)

Here’s how it works. Let’s assume the formula refers to Albania, the 3rd country in the list:

LEN returns the length of the country’s name.

INDIRECT(“1:”& LEN($A4)-2) evaluates to (1:5).

ROW(INDIRECT(“1:”& LEN($A4)-2)) evaluates to {1;2;3;4;5}. This tells the array formula which characters in “Albania” to evaluate against their respective two next characters. Using ROW this way is very handy in array formulas.

CODE(MID(LOWER($A4),ROW(INDIRECT(“1:”& LEN($A4)-2)),1)) resolves to {97;108;98;97;110}. In other words, the numeric values of the first five letters of Albania are 97, 108, etc. CODE is the function that returns the values. LOWER converts each letter to lower case, otherwise, for example, a lowercase “b” wouldn’t be seen as following an uppercase “A”. The MID piece tells the array formula to parse each of the first 5 letters in the word.

Okay, now we’re getting close. This bit:
CODE(MID(LOWER($A4),ROW(INDIRECT(“1:”& LEN($A4)-2)),1))+1=
CODE(MID(LOWER($A4),ROW(INDIRECT(“2:”& LEN($A4)-1)),1)) resolves to
{98;109;99;98;111}={108;98;97;110;105}, which in turn resolves to {FALSE;FALSE;FALSE;FALSE;FALSE}. In other words, one added to the code for the first five letters in “Albania” doesn’t ever equal the code for their respective next letters. So there’s not even two consecutive letters. What a country! The next two lines in the formula repeat the comparison, this time with the 2nd through 7th letters.

The 2nd line, which starts with MID, and the 2nd to last line say that if the whole mess is true, return the three letters that start at that position.

The IFERROR function at the beginning and the last line simply say that if the formula errors (doesn’t find three consecutive letters) put a blank in the cell. IFERROR is available in Excel 2007 and 2010. I like it very much as they keep formulas like this from being twice as ridiculously long.

Much of the evaluation above was done using the F9 key. Selecting part of a formula and then pressing F9 evaluates that piece of the formula, a very useful feature.

There are only two countries that meet the criteria, Afghanistan and Tuvalu. I figured it was probably the former and soon enough realized the article of clothing was “hijab.”

If you’ve made it this far, thanks! Oh yeah, the multiple cell and conditional formatting way works similarly to the array formula, only by breaking it up it’s a lot easier.

If anybody has a better array formula, please share.

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:

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:

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:

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.