ICAEW Excel tips: Hammer and nails
It’s an old but respected saying – “When all you have is a hammer, every problem looks like a nail.” I had an experience this month which really brought this to home.
I typically default to using Excel for most everything. Whether it’s recordkeeping, accounting, analysis, or laying out my holiday calendar or whatever it may be, my lengthy experience with Excel has meant that it’s my go-to application. And indeed, when I’m trying to solve problems, I usually unconsciously start to shape them in terms of how I might do them in a spreadsheet.
But it’s all too easy to get het up on one particular mode of thinking, and hence get blinded to the fact that you aren’t taking the right approach.
This week, I heard about an interesting maths puzzle which I wanted to try. In case you’re curious, it was this one:
Arrange the digits 0 - 9 into a 10 digit number, such that the first two digits read as a number are a multiple of 2, the first three digits are a multiple of 3, and so on.
For example: 1,234,567,890:
12 is 2x6 – OK
123 is 3x41 – OK
1,234 is not divisible by 4 - FAIL
As it happens, there is only one possible solution to this requirement. I began thinking about this and as I did, I started thinking about how to do it in Excel.
I came up with some formulas using LEFT and MOD to check if the first N digits were divisible by N. I used SEARCH to check that all digits 0-9 were present. But, the number of candidates – the numbers from 1,000,000,000 to 9,999,999,999 – was simply too large.
I then tried to work this into VBA. Eventually, I had some code that could check a number, but again, the number of candidates was simply too large to compute. Even with a little pencil and paper work to reduce the candidates (the number must end in 0, for example), I just couldn’t get a reasonable solution.
Eventually, I conceded that, while it might be possible, brute force in Excel just wasn’t the right way to attack my problem. Instead, some written-out mathematics and logic were the right route.
The whole experience was a humbling reminder that expertise alone won’t solve all your problems. Sometimes, you have to look beyond Excel, and realise that your problem might not be a nail after all.
Tip of the Week highlight – WEEKDAY
This month’s highlight is drawn from TOTW #197 and discusses a useful little function for converting dates into days of the week.
If you have a properly formatted date in one cell, you can use WEEKDAY to figure out its position in the week; the syntax is:
=WEEKDAY(input date cell reference, return type)
The “return type” lets you choose how the position in the week is identified:
I usually use 2, but any standard can work. So for example, if you point this formula at a cell with 14/08/2017 in it, you will get back the number 1, indicating that it’s a Monday.
If you want to return the word “Monday” instead, you have a couple of options:
The left example uses an INDEX to pull the chosen name from a list, using WEEKDAY to steer the INDEX to the appropriate cell. The right-hand option is a completely different approach – if you format a date cell with the custom format “dddd”, it will display the name of the day of the week. You could also use “ddd” to get the three-letter abbreviation for the day.
Principle of the Month
This month’s selection from the Twenty Principles for Good Spreadsheet Practice is #12:
Be consistent in the use of formulae.
There are hundreds of formulas within Excel, and many overlap with one another. Reducing and standardising the list of functions to the fewest possible will help to reduce the required knowledge and mental space of any user. For example, SUMIF can only be written with one requirement, but SUMIFS can have one or more – but has its inputs in a different order. Always using SUMIFS, even in cases with just one requirement, will simplify the user’s journey to understand your workbook.
You should also be consistent in standardising what kinds of formula syntax and standards you use – for example, do you use FALSE or 0 for VLOOKUP? Do you use multiply by 1 for converting text to numbers, or the VALUE function, or something else? Are IF functions written with line breaks? And it’s not enough just to standardise your own use – you have to work with colleagues to try and build a common consensus wherever possible.
David Lyford-Smith is the technical manager in the ICAEW’s IT Faculty and blogs for the ICAEW Excel Community. The Excel Community is an Excel content hub that encompasses webinars, blogs, member Q&A, fortnightly bulletin updates, and more.