This last month has been an interesting one in terms of blog themes.
In each of these monthly articles, you have probably noticed that I highlight a post from my own weekly Excel blog, the Tip of the Week. ICAEW also features a few other bloggers in its rotation, and in February we accidentally created something of a theme month.
Early in the month, I wrote a post about SUMPRODUCT (if you are an Excel Community member you can read it here). It’s not the first time I’ve covered the subject – nor will it be the last. The function is flexible, powerful, and can replace or improve upon many other similar functions.
I write my columns a few weeks in advance. But unbeknownst to either of us, I was engaged in some parallel thinking with Simon Hurst (who also blogs for this site).
Simon posted his own article about comparing calculation speeds between VLOOKUP, INDEX MATCH, and, yes, SUMPRODUCT (again here for Excel Community members). He made some interesting observations about how different approaches can affect calculation speed in a large workbook, via a little trial and error (and a stopwatch!).
Having then picked up on this theme, modeller Liam Bastick decided to chip in with a SUMPRODUCT article of his own – discussing how the SIGN function can be used with more complex array functions to help create a test that adds all numbers meeting any of a range of specified conditions.
All this spun out of just one function! The depth of Excel never ceases to amaze me.
Tip of the week highlight: Finding items that add to a target
Ever had a problem like this one? You receive a payment into your bank for £1,373.32 from a customer of yours. You look at your list of invoices outstanding from this customer and see this:
Which invoices is the payment for? We can use the Solver Add-In (which comes with Excel) to find out. You can enable Solver from the File > Options menu.
We need to set up our data like so:
We are here using 1s and 0s to indicate which amounts are included in our current guess. We then set up Solver like this:
And then Solve!
Just be cautious: If your list has more than a dozen or so invoices, it can take a very long time to find a solution, and there might even be multiple solutions.
Spreadsheet competency of the month
The Spreadsheet Competency Framework was created by ICAEW to provide a guide to how to talk about spreadsheet knowledge and ability, and what skills are needed for which roles.
The second level is General User. General Users make up the majority of spreadsheet users, essentially modifying and updating spreadsheets on a regular basis. They may have some formula and other more technical knowledge, but are rarely called upon to undertake highly complex tasks, or to make spreadsheets entirely from scratch.
Find out more about the definitions, and the list of skills needed for each, at the link above.