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.
Make short formulae with few functions
We need to keep our formulae short and using few functions. A complex formula may need to be split into multiple steps, across several cells. That is, use "helper columns" (or rows) to do a complex calculation in steps rather than all in a single formula.
As a guide, any formula that is longer than 100 characters, or uses 4 or more functions, is a candidate for simplification.
Trade-off between simple and atomization
However, there is a trade-off between the complexity of an individual formula and the aggregate complexity of a spreadsheet.
Dividing formulae into many small parts – atomization – leads to a proliferation of formulae and can result in long arcs of precedence (i.e., long sequence of cells in a calculation chain), which can be difficult to untangle. A highly atomised spreadsheet may be more difficult to understand than one that has fewer, slightly more complex formulae.
Seek a balance between formula complexity, atomization, and length of precedence arcs – with the goal of making the spreadsheet easy to understand, use, and change.
