ICAEW Excel tips: Excel community goes data-gazing
In this month’s blog, The IT faculty’s David Lyford-Smith goes data-gazing with the Excel Community Advisory Committee at the Data Observatory
The Excel advisory committee is the volunteer group of members that steers the ICAEW’s direction in spreadsheet activities.
At our most recent quarterly meeting, we were lucky enough to be hosted by the Data Science Institute at Imperial College, in advance of a joint event we are running with them on Monday 19 September about the Future of Spreadsheets.
The Institute’s main project is a massive circle, built four high, of 64 HD 47-inch monitors, connected to a network of 32 computers, designed for data visualisation at its largest.
For example, a small slice of the screens showed this real-time bitcoin visualisation:
The real meat for us, however, was when a single, 64-tab engineering model was displayed on every tab simultaneously. The setup even allowed for a genetic algorithm to test different variables in the model, ending up with every screen showing a different possible set of starting conditions and summarising the fitness of each against a range of KPIs.
Fancy toys like the Observatory may just be for research for now, but it was certainly an eye-opener to see what might be possible with more powerful computing in the future. I’ll certainly update in next month’s blog after the Future of Spreadsheets event with more thoughts!
Tip of the Week highlight – How many different types of item are there?
This one comes from a more recent post in my ICAEW blog Excel Tip of the Week – it’s #142 from back in July. It’s one of my favourite little formula cheats.
The problem at hand is this one: column A has a list of recently sold items. What we want is a formula that can tell us easily how many different items sold at least one unit. Whilst we could use a data analysis tool (such as Remove Duplicates or a PivotTable) to derive this number, those methods are slow and require re-performance each time the list in column A changes. We need a formula to do it instead.
There isn’t a built-in Excel function for what we need. But a very sneaky combination of two functions can do it. Let’s say the list is in A1:A100:
There is some clever mathematics behind this that makes it work; if you want to know more, try using ‘Evaluate Formula’ to see how it operates.
Principle of the Month
This month’s selection from the 20 Principles for Good Spreadsheet Practice (www.icaew.com/excel20principles) is #18:
Rigorously test the workbook.
Academic review of spreadsheets shows a few important facts. Renowned spreadsheet error researcher Ray Panko showed that around 1% of formula cells contain an error – and with most significant spreadsheets consisting of hundreds if not thousands of formulas, the overall spreadsheet-wide error rate is around 80%. What’s more, even careful self-review will catch at most half of the previously uncaught errors.
Proper testing is an art into itself, with many techniques and elements. But it is also absolutely vital to not only test your own workbooks, but for spreadsheet of significance, to engage in peer review as well.
David Lyford-Smith is a 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. Find out more at this link.