Version 0.1
Preamble
Microsoft Excel is so famously flexible that it has become pervasive in nearly every field of endeavor, from art to finance to science and engineering. But science and engineering are not well served by flexibility when that means ambiguity. I have personally corrected or redesigned dozens of my clients’ science and engineering spreadsheets that arrived in error, intractable, or both.
Financial professionals have launched several attempts at spreadsheet standards, including The Best Practice Spreadsheet Modeling Standards (“BPM”) and the FAST Standard. Though carefully thought through and well-intentioned, none of these have succeeded in becoming a de rigueur standard in the financial professions. Thus far, the science & engineering professions have not enjoyed even this level of success in deploying a spreadsheet standard. Though it is a less than perfect fit for my work, I have been partially applying BPM while I wait for a more appropriate standard to appear. After years of waiting, that has not happened. So, I’m doing it myself.
Science and engineering probably put more variegated pressures on spreadsheet software than financial modeling, so I have chosen a leaner approach than used for BPM and FAST, featuring just the nine Principles below. Maybe this will give Spreadsheet Standards for Engineering & Science a better chance at gaining traction, and improving my and my colleagues’ quantitative work products.
1. Determinism
A workbook must create a singular set of output values for each unique set of user inputs.
Corollary: When multiple sets of user inputs need to be evaluated for a single study, then computation from each set of inputs shall be preserved in a separate, archival copy of the workbook.
2. Computational Chaining
Every numeric value in the workbook that is not a constant or a user input, shall be chained through formulaic relationships to constants or user inputs.
3. Comprehensible Formulae
Each formula shall be constructed sufficiently simply that its function is instantly recognizable to a trained scientist. Functions too complex to meet this criterion shall be chained across multiple cells until each formula meets the criterion.
Recommended implementation in Microsoft Excel: No formula may contain more than eight referenced ranges.
4. Units
Every numeric value shall be labeled with its physical units (including an explicit statement that the value is unitless when that is the case).
5. Source Citation
Every constant shall be in a dedicated cell and accompanied by a source citation.
Corollary: No equation in the workbook shall include a hard-coded constant other than 1.
6. Distinct User Inputs
Every user input shall be prominently identifiable as such.
7. Descriptive text
Every user input, constant, and formula shall be explained with descriptive text. Descriptive text may apply to multiple spreadsheet cells simultaneously so long as formatting, or the descriptive text itself, makes clear to which cells the descriptive text applies.
8. Transparency
All constants and formulae in the workbook shall be equally visually accessible.
Recommended implementation in Microsoft Excel: Prohibit hidden rows, hidden columns, and hidden sheets.
9. Aesthetic Uniformity
Each spreadsheet element shall carry its own unique formatting style, and the formatting style shall be applied to all such elements.
Definition: “Spreadsheet elements” includes but are not limited to (a) user inputs, (b) constants, (c) source citations, (d) formulae, and (e) descriptive text.
Extension: A key to element styles shall be prominently placed in the workbook.