Spreadsheet Check and Control

Spreadsheet Check and Control by Patrick O'Beirne

Spreadsheet Check and Control
47 key practices to detect and prevent errors

Patrick O’Beirne BSc, MA, FICS
ISBN 1-905404-00-X
Published by Systems Publishing, Gorey, Ireland 2005
194 pages, € 29.95

Order directly from the publisher

47 key practices to detect and prevent errors

Although this book was written with the syllabus of The European Computer Driving Licence (ECDL) in mind, its quality and depth far surpass any expectations that may be implied by that fact. Patrick O’Beirne’s wide experience of spreadsheet pitfalls and risk is apparent at every turn, resulting in a serious discussion of how best to avoid business disasters based on inaccurate and erroneous Excel models.

Microsoft Excel – and other spreadsheet programmes – allows the creation of models of infinite complexity. The main advantage of Excel - an easy-to-use, almost intuitive, “programming” interface – is potentially its main disadvantage. Spreadsheets get started quickly and may or may not be properly maintained. Complex calculations can be resolved in a cell-by-cell iterative manner, making error location difficult. Solutions tend to be extended over time – often by different people – which compounds the problem. A myriad of pre-packaged functions is available, but the choice of the appropriate – or inappropriate – function is entirely at the developer’s discretion.

Spreadsheet Check and Control addresses all of these issues by providing succinct, down-to-earth tips on the basics of spreadsheet working:
• Specification – define what you’re trying to do, record sources of information
• Security – avoid breaking a working version, making sure everyone is using the correct version, the use of passwords and their limitation
• Style – managing constants, units of measurement etc for consistency, handling unwieldy formulae

The discussion on Calculation addresses the fundamentals, such as the precedence of mathematical operators, circular references, and the particular quirks of arrays in Excel. Valuable hints on finding and identifying errors are provided followed by detailed descriptions of various error types and explicit tips for their correction. Obviously, if the developer understands the generic cause of an error type, then finding the offending formula or data for a specific instance is a much more efficient process. Given that the nature of the Excel beast results in error displays that are often “separated in time and space” from the original cause, the guidance provided here will significantly reduce error tracking effort.

The section on Output includes the mandatory hints on charting, but the pitfalls associated with cell formats provide answers to some fascinating questions: When is a number not a number? Are invisible cells seen by some? When is 100% not 100%? When did John Smith change his name? When is a database not a database?

Although it was common enough in the early days of programming, no one today would claim that a software programme was completed without some degree of formal testing. Yet spreadsheets are constantly being modified and altered – not just before that important meeting, but actually in the meeting! – without much awareness of the need to test the quality of the changed model. Patrick’s work as Chairman of the European Spreadsheet Risk Interest Group (EuSpRIG) provides him with numerous examples and anecdotes of just how horribly wrong things can go as a result. The Review section of the book addresses the key areas of data integrity and functional correctness and is quite simply a “must read”.

The Appendices provide useful information, but my favourite is the list of websites and software tools, which provide all the links the user needs to pursue the topic of Spreadsheet Check and Control further.

If you need useful, practical advice on building better spreadsheets, then this book is for you.

If you need to have confidence in the models that your colleagues and employees build, then this is the ideal present for them.

My only gripe has nothing to do with the book, but with the fact the Microsoft do a relatively good job of translating function names and arguments. As a result, it does require some additional brainpower to get the best of Patrick’s tips if you’re using a foreign language version of Excel.

del.icio.us  Digg It! furl  Google Bookmarks  Netscape  Reddit SlashDot  Sphere  Spurl StumbleUpon  technocrati Windows Live  Mr. Wong  Yahoo MyWeb
Modelling | Sources | Books |

Recommend    Comment