Spreadsheet good practice guidelines
Formulae
Formulae are the heart of a spreadsheet. It is primarily through writing formulae, a form of computer programming, that we create the many and varied analyses for which spreadsheets are renowned.
With more than 500 functions, the ways in which we can write formulae is vast. For almost any problem, there are many ways to use and combine functions to solve that problem.
Many formulae are simple, while some are complex. Overall, we know from extensive research that 1% to 5% of all formulae contain at least one error, and 95% of spreadsheets are wrong. We must do better.
Surprisingly, many spreadsheets have no formulae. For example, the FUSE corpus contains around 250,000 unique spreadsheets found on public websites. Only 7% of those spreadsheets contain any formulae. Perhaps that reflects the type spreadsheets that people put on websites. But a lack of formulae is common in a corporate environment too – of the over 15,000 unique spreadsheets extracted from Enron emails, almost half contain no formulae.
But when we do write formulae, there are many tips and traps that we need to be aware of. This section presents guidelines about how to write better formulae, so we can make fewer errors and have spreadsheets that are easy to use, easier to understand, and trustworthy.
Guidelines:
- Formulae are short, simple, and easy to understand.
- Formulae are not atomised to the extent that they become difficult to understand in aggregate.
Complex formulae are difficult to understand – often even for the person that created them. Complex formulae are also more likely to have errors.
To reduce these issues, make formulae easy to understand.
Guidelines:
- Avoid using spaces and new lines in formulae.
- Any spaces and new lines in formulae do not cause unintended side-effects.
Adding spaces and and new lines to formulae can make them more readable. But they are not passive characters – they are the "intersection" operator, which can inadvertently change the result.
