ExcelZone: EuSpRIG 2017 round up
David Lyford Smith reports back from the EuSpRIG spreadsheet conference held at the Imperial College.
It was my pleasure this month to attend the 2017 meeting of EuSpRIG – the European Spreadsheet Risk Interest Group. EuSpRIG is an academic conference centred on the issues of spreadsheet risk – what causes it, what effects it has, and how it can be tackled.
The conference speakers covered a wide variety of topics, but I just wanted to highlight a few of my impressions from the day.
First of all, David Birch presented a paper on ‘The Future of Spreadsheets’, which was a direct follow-up from the joint ICAEW/Imperial event of the same name which ran last year – and which was also covered previously in this blog.
David presented some key themes and lessons that the event attendees had identified, and spoke on the reasons behind Excel’s popularity, the limits on it, and how the future trends in spreadsheets are likely to attack those limitations.
Paul Mireault spoke about best practice, and how trying to both design and mechanically create a spreadsheet simultaneously is likely to lead to mistakes. These, Paul argued, are both high-complexity tasks, and should not be juggled – instead diagramming out the design of a spreadsheet should come first, and the construction only after that.
Barrister Stephen Mason then spoke about the position of English law on spreadsheets – and in particular how the law somewhat alarmingly presumes that the products of computer work are accurate! The law seems at present not to account for the fact that small errors in computations are easy to make and can be near-impossible to detect, a fact that another speaker Grenville Croll confirmed. Grenville spoke of a case study of a model review that took hundreds of hours to find a critical mistake that had been entirely missed by both the creators and the users of the spreadsheet.
At the end of a long day, I came away with the same energy I remember from last year’s conference – the risks and problems with spreadsheet use are as prevalent as ever, and that means that the job of improving people’s understanding of those risks, and sharpening their skills to help them avoid those risks and inefficiencies, is as important as ever.
Tip of the Week highlight – Named cells
This week’s highlight is based on the ICAEW Excel Tip of the Week #188. The topic is named cells and ranges, and how to work with them.
The simplest application is to select a cell or range, and then use either Formulas => Define Name or use the Name Box at the top-left of Excel to enter a chosen name:
Once created, you can then use the name of a cell to call it in functions. This can add readability to your workbook, and also simplify calling a constant or input data on some far-away worksheet. But going overboard on names can make things a bit tricky to get in to, so do carefully consider where you put the balance.
All the named cells in a workbook can be identified via the Name Manager, on the Formulas tab:
Careful use of functions like CHOOSE or OFFSET can even let you create dynamic ranges, which will adjust in size and location based on various input cells.
Finally, if your data has labels to the left / above it, you can use the Create from Selection command to automatically add names to your rows / columns based on those labels. Just be careful, as some characters aren’t permitted in names (the hyphen for example) and so the result may be a little different to what you expected.
Principle of the Month
This month’s selection from the Twenty Principles for Good Spreadsheet Practice is #13:
Keep formulae short and simple.
This one is sometimes a real challenge. Getting a formula to do exactly what you need it to do is sometimes a matter of engineering an increasingly complex series of nested and interacting arguments. The problem is always to balance the need to get the result you need against the problems of long formulas – that is, the likelihood of making errors, and the difficulty for others to review and comprehend them.
One particular trick is to try and split very long calculations into stages. Using one or more helper columns can help make it easier to follow the flow of a calculation, and also reduce the chance of errors as you see each stage separately and minimise a number of nested formulas you need to mentally unpick.
Ultimately, there isn’t always a short solution to every problem, but there’s also no prize for the fewest cells used: if breaking a solution down a bit would help, then do it. You can always group and minimise helper columns if need be.
See you next month,
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.