ICAEW Excel tips: Excel is some product

Excel
istock_a-wrangler_sp
David Lyford-Smith
Columnist
Share this content
Tags

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

This week’s tip is based on Excel Tip of the Week #225 from ICAEW’s Excel Community.

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:

1

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:

2

 

We are here using 1s and 0s to indicate which amounts are included in our current guess.  We then set up Solver like this:

3

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.

See you next month,

~David

About David Lyford-Smith

David Lyford Smith

David Lyford-Smith is the technical manager for ICAEW, where he works in the IT Faculty and manages and blogs for the Excel Community.

 

Replies

Please login or register to join the discussion.

avatar
15th Mar 2018 14:27

Love this tip - hope I get to use it on something soon!

(Apologies if I seem super keen - just starting to expand my Excel knowledge as it has been quite basic - and I am amazed at how many things it can actually do!)

Do you know if there is a document or guide or something available that briefly explains each of the different functions so I know which ones I can focus in on first (i.e. which ones I think I will be able to use/find useful first)

Thanks (0)