Authors
Angus Dunn
Abstract
Spreadsheets are ubiquitous, heavily relied on throughout vast swathes of finance, commerce, industry, academia and Government. They are also acknowledged to be extraordinarily and unacceptably prone to error.
If these two points are accepted, it has to follow that their uncontrolled use has the potential to inflict considerable damage. One approach to controlling such error should be to define as "good practice" a set of characteristics that a spreadsheet must possess and as "bad practice" another set that it must avoid.
Defining such characteristics should, in principle, perfectly do-able. However, being able to say with authority at a definite moment that any particular spreadsheet complies with these characteristics is very much more difficult.
The author asserts that the use of automated spreadsheet development could markedly help in ensuring and demonstrating such compliance.
Sample
Suggested code of good and bad spreadsheet practice.
Examples of characteristics from the suggested code:
- Required: Inputs and calculations to be modular in design.
- Required: Each section should be independently checked.
- Desirable: A spreadsheet should be easy to read.
- Desirable: There must be extensive use of self checking.
- Avoid: Inclusion of hard coded constants in formulae.
- Avoid: The use of formulae of considerable complexity.
- Banned: Hard coded constants in calculation areas.
- Banned: Hidden cells and hidden sheets.
Publication
2010, EuSpRIG
