## L4542: ‘Have Fun, It’s Wordy’ by Kea

Posted by Encota on 8 March 2019

*If* you were a setter (or editor) trying to encourage some of the ‘I-only-solve-48-out-of-52’ Listener solvers – you know the ones (and it may well be *you*!), those who see the four numericals each year and suddenly decide that they are really busy that day, the front door step really does need sweeping etc – to try a numerical, then *what Title* might you give a Listener Numerical to encourage the more word-oriented solvers to have a go? Clearly the answer is: “**Have Fun, It’s Wordy**“, as Kea has chosen for this week’s puzzle.

As I’ve mentioned in earlier blogs, I always love it when the puzzle number plays some part in the overall story. Here the puzzle’s number 4542 is used as the example in the preamble which, when spelt out with repeated letters suppressed, gives FOURTHSANDIVEYW. Which, *naturally*, is a jumble of HAVE FUN, IT’S WORDY. **Delightful – who needs the puzzle itself after that level of elegance – thanks Kea!!**

I think in the past I have identified up to seven different levels of ‘assist’ one can have in Numericals. Today I’ll pick on three and then focus on one, the use of Excel to help with solving. Most people have access to Excel on one machine somewhere or other – especially those people who read blogs. So, the three levels:

- Pure pencil and paper only
- Assist using a spreadsheet program – hereafter known as Excel 😉
- Custom coding. Nowadays this is often in Python, though C, BASIC and many others are often used, too. This is normally a fairly accurate test of the coder’s age: I use C, for example (but only when pushed!). And anyone this week loading their program serially on paper tape into an Elliott 703 computer, then do leave me a Comment 🙂

For those who like type (1) then this week you’ll have been in for a treat, as the puzzle is reasonably solvable by this means. Spotting that at least nine of the 3-digit entries are used as the arithmetic expression for other letters (in clues B, C, D, E, H and c, e, f plus, by inference, k) is useful. Also noting that, what with the letters BCJKMPQZ not being used in appropriately-sized word-based answers (after all, for example, no ZILLIONS are featured 😉 ) then the max total of all remaining letters is <= 253 and thus all such *3-digit* entries at B, C, D, E, H etc must begin with a 1 or a 2. G and H both appearing multiple times as – or in – arithmetic expressions helps a lot, too. And, given its length of (6) [and that a (down) cannot begin with a 0], A can immediately be narrowed down to either 11 or 12, with its associated ‘n’ being either 53 or 82. In good, traditional fashion I will leave the rest as an exercise for the reader 🙂

For those of you already using type (3), then you’ll most likely already know more than me on the subject, so I will move on very quickly …

So, today – Excel! A program almost too powerful for its own good, the basic user barely gets beyond adding and multiplying, with a bit of formatting to make the output look more readable. [And, if an accountant or analyst, there will be numerous cases of calculating *Ratios*, as I’ve heard that an accountant’s life isn’t complete without Ratios: Pens per Employee, P/E ratios etc. I digress …]

Useful functions. Intro: if you rarely or never use functions, don’t be frightened. Select a cell in a new Excel workbook, type in an = sign followed immediately by the start of the function name. A selection will appear. Click/ double-click on the one you want, then fill out the brackets with the items prompted, often by clicking in other cells to select them, and then close with a final bracket and press return.

Things I used (or could have used) in my Excel solving route for L4542:

**=VLOOKUP()**. Very useful if you want to select a value associated with, say, a letter. I had a table with:

A 1

D 4

etc. in it, in adjacent cells. Using the whole table as part of the input meant that calculating sums of letter values was easy.**=CODE()**. This useful function returns the ASCII value of a character in a chosen cell. If that sounds like gobbledy-gook then, yes, it is. However, if you take 96 away from the answer for lower-case answers, or 64 for upper-case answers, then you get L4542’s ‘value’ of each letter. Try it! Type the letter T in cell A12, then ‘=CODE(A12)-64’ (ignoring the quote marks) in cell A13. Repeat as required. Easy!- the
**$**sign in a formula. Apologies for those to whom this is obvious – but I have seen grown men(!) struggle with this! I read the $ sign as a ‘stick’ sign.

If you put, say, ’10’ in cell A1 and ‘=2*A1’ in cell B1, then the value displayed in B1 will of course be 20. If you now copy that formula into C1 by the click&dragging of its bottom right small square, Excel will be ‘clever’ and assume you wanted C1’s contents to equal 2*B1. But what if you wanted it to be 2*A1 still (for some reason)? Then in B1 change its formula to ‘=2*$A1’ and press return, then click&drag that into C1 and you’ll have what you want, it will ‘stick’ with A. Now use this to create a simple Times Table Square, with say 1 to 10 along the top and the side, where each content uses something like ‘=$A2*B$1’. when you drag this formula into other cells then the A will ‘stick’ and the 1 will ‘stick’ with the other adjusting to match the position of the cell. So a distant cell might become ‘=$A10*J$1’. Much easier to do than describe. Try it! Use the built-in help (or Auntie Google) for support. - String handling functions. A posh way of saying dealing with words. Sort of …
**=CONCATENATE()**joins together all strings separated by commas in the brackets. So if cell A1 contained the wordstring EIGHTY and A2 contained SEVEN, then the formulae (in, say, cell A3), =CONCATENATE(A1,A2), will have the value EIGHTYSEVEN.*If*you wanted a space between them you could add the string containing a space**=CONCATENATE(A1,” “,A2)****=LEN()**gives the number of characters in a string. Typing in A4,**=LEN(A3)**, will display a value of 11 or 12, depending if you’d added the space above or not. In solving L4542 it is probably better not to!

- With those functions, plus the usual ‘=SUM()’, much of this puzzle can be solved much more easily.
- Additional things you may wish to explore, if you are feeling keen:
- Selecting a character in a string. Try out ‘
**=RIGHT()**‘ and similar, carefully following the syntax that Excel prompts you for. You can use this multiple times, nesting them, if it helps. - Excel’s ‘Data’ menu’s
**Text to Columns**and**Remove Duplicates**(i.e. duplicate rows). To try the latter, type the characters N, I, N, E, T, Y in six adjacent cells in a*column*. Select them all and press**Remove Duplicates**on the Data menu. It will delete any rows that are duplicates, in this case resulting in NIETY in a column. Combining that with CODE or VLOOKUP in the adjacent column and summing the results is one way of getting a spelt-out total for a word. **Conditional Formatting**on the Home menu. If you wanted to highlight all cells in the sheet containing, say, the value 24, then Select all cells (i.e. click on the triangle at top left of the sheet so all go light blue) and follow the instructions, picking a bright colour of your choice. You can then easily scan a small spreadsheet to spot which ones are of more interest. Again there’s no substitute for trying it! There are of course better tools if the size of the spreadsheet is becoming significantly larger, though beyond the scope of this blog!

- Selecting a character in a string. Try out ‘

That’s plenty enough for one blog. If you have any tips that you think would be useful to those who’d like to use Excel to help them solve numericals then do please post Comments. And if this has been useful to just one solver than it has been worth writing!!

Cheers,

Tim / Encota

## annechambers said

I used MID() to extract each letter from the string, and FIND() to see if the same letter occurred later in the string and hence ignore duplicates. IFERROR() is useful when a function returns N/A or ERROR and you really want 0 instead.

## Andy Wallace said

I used perl. There’s a module on CPAN called Lingua::EN::Numbers that converts digits to strings in exactly the right format, e.g. 4542 becomes “four thousand five hundred and forty-two”. From there, removing spaces, hyphens and duplicate letters is trivial, as is counting the letter sum. So I just looped it through all numbers from 10 to 999 and output to a file in similar format to the clues, then just searched it with grep.

## Steve Tregidgo said

Thanks for the tips Tim — as with your excellent wordy primer recently, this will surely help a lot of people to get started with numerics. (I came to The Listener for the numerics and stayed for the words, but I do like the quarterly change of pace.)

For once I decided to forego Python in favour of pencil and paper, as an attempt at that “pure” solving experience. I wrote out some tables of the numbers I needed (1-19, 20-90 in tens, HUNDRED and AND), along with their two associated numbers. After that it became fairly easy to combine them when I wanted to test a number (the unique-letter parts taking longer as I couldn’t always use the crib sheet).

After solving I used Python to check all my answers satisfied the clues. I replicated my tables (mapping numbers to words; the code did all the sums) then wrote a short function to turn a given number into a phrase — not as hard as it sounds:

* If the number is at least 100, it must start with (the word for) a single digit and the word HUNDRED.

* If the number is not an exact multiple of 100, it must then have the word AND.

* Whatever has happened above, the hundreds part of the number can be discarded.

* If the (hundreds-less) number is twenty or more, count the tens and look up the -TY word in a table. Discard the tens part in this case.

* Whatever’s left is either a number less than twenty, or a number less than ten plus the -TY word we got above. Look up the word in a table.

This turns 125 into “ONEHUNDREDANDTWENTYFIVE”. The length is then easy in Python – ‘len(NUMBER_STRING)’ – and so is the awkward total. Assuming a lookup table of letters to numbers (but the ASCII subtraction trick is just as good), it’s ‘sum(LETTER_VALUES[l] for l in set(NUMBER_STRING))’ (‘set’ discards the duplicates).

At this point I just wrote out one more table of clue letters to my answers, and ran the code on it so I could visually check all the clues were satisfied. I imagine it would have been possible to build a solver this way too though — programming in more tables of clue expressions and of entry digit relationships. I’ve solved a few numerics with help from Python, usually to do the heavy lifting on an answer check (or to break something if it looks like brute force is required, usually meaning I’ve missed a more elegant trick) but haven’t yet gone as far as utilising a general constraint solver like that. One day I’ll find the time to try it out!

For fun I tried starting something in Excel. With sheets named UnderTwenty and Tens, each containing a table mapping numbers to words (with headings, so the data started at A2 in both cases), I then made a third sheet with row 2 like this:

A: (number to decompose, to be entered)

B: hundreds value: =FLOOR(A2/100, 1)

C: remainder after hundreds removed: =MOD(A2, 100)

D: tens value if at least twenty (ie not 10 itself): =IF(C2>=20,FLOOR(C2/10, 1), 0)*10

E: units-or-teens value (ie whatever is under 20): =IF(C2>=20,MOD(C2, 10),MOD(C2, 20))

F: Number of hundreds: =IF(B2>0,VLOOKUP(B2,UnderTwenty!$A$2:$B$20,2,FALSE),””)

G: HUNDRED if needed: =IF(B2>0,”hundred”,””)

H: AND if needed: =IF(AND(MOD(A2, 100)0, A2>=100),”and”,””)

I: -TY word: =IF(D20,VLOOKUP(D2,Tens!$A$2:$B$9,2,FALSE),””)

J: The rest: =IF(E20,VLOOKUP(E2,UnderTwenty!$A$2:$B$20,2,FALSE),””)

K: All together: =CONCAT(F2:J2)

Copy the row down and every number from 1-999 that you enter in column A will be described in words in column K. At this point, getting the length should be easy, but I believe getting the unique letters requires a bit of VBA (basically, defining your own function). Could be worth doing though as with that done, any number can be turned into any pair of length/unique-value-sum numbers at will. (Or you can make 253 rows, fill the first column with numbers 1-253, and just use the whole sheet as a manual lookup table.)