Ensuring accuracy in spreadsheet calculations

Abstract

Spreadsheet programs have become ubiquitous and indispensable in the laboratory. Data may be easily entered and manipulated without the need to learn a programming language or consult a statistician. Spreadsheets are numerically robust and easily altered with a few mouse-clicks to accommodate changing needs. Ease of use, however, implies ease of making errors. Data can be entered in place of equations when a sheet is expanded, sometimes without immediately obvious consequences. Errors can be made less likely to occur by paying attention to the design of the worksheet as it is constructed. Input cells can be formatted to delineate input clearly from output. The sheet can be annotated with titles and notes. Named variables aid understanding and error tracing. Modular blocks of code can add clarity. Logical tests should be included to allow for the occurrence of blank cells, zeroes, and negative numbers. The output should display the values of the important parameters used in the calculation as well as the final result. A finished worksheet needs to be tested to assure accuracy. Standard test data should be used to recheck a sheet‘s reliability after it is modified