4.5 Development and Validation of Spreadsheets for Calculation of Data

• 4.5.1 Introduction
• 4.5.2 Development of Spreadsheets
• 4.5.3 Validation of Spreadsheets

When using spreadsheets or programmable calculators for reduction of data generated by sample analyses, there should be assurance that the results are valid and usable for regulatory use. The following section provides guidance for assuring that spreadsheets will meet these criteria.

4.5.1 Introduction

Although the formulas given above for calculation of statistical parameters may seem complicated, matters are simplified by the ready availability of spreadsheets and calculators which provide these values transparently. This makes calculation of statistical parameters much more straightforward than in the past, when direct application of these formulas was used. It is still useful to have some familiarity with these formulas to understand how statistical parameters are derived. In addition, there may be a need to verify the results of statistical data generated by a spreadsheet or calculator; data can be plugged directly into the formulas above to verify these results.

4.5.2 Development of Spreadsheets

Excel® and other spreadsheets incorporate all of the statistical parameters discussed, as well as many others. Although individual spreadsheet functions can be considered as reliable, it is important to make sure that data is presented to the spreadsheet with the proper syntax. Also, when spreadsheets are used for multiple numerical calculations in the form of in-house developed templates, it is important to protect the spreadsheet from inadvertent changes, to verify the reliability of the spreadsheet by comparison with known results from known data, and to ensure that the spreadsheet can handle unforeseen data input needs. Spreadsheets developed in the ORA laboratory should be looked upon as in-house developed software that should be qualified before use, just as instruments are qualified before use.

4.5.3 Validation of Spreadsheets

General guidance for design and validation of in-house spreadsheets and other numerical calculation programs includes the following considerations:

• Lock all cells of a spreadsheet, except those needed by the user to input data.
• Make spreadsheets read-only, with password protection, so that only authorized users can alter the spreadsheet.
• Design the spreadsheet so that data outside acceptable conditions is rejected (for example, reject non-numerical inputs).
• Manually verify spreadsheet calculations by entering data at extreme values, as well as at expected values, to assess the ruggedness of the spreadsheet.
• Test the spreadsheet by entering nonsensical data (for example alphabetical inputs, <CTRL> sequences, etc.).
• Keep a permanent record of all cell formulas when the spreadsheet has been developed. Document all changes made to the spreadsheet and control using a system of version numbers with documentation.
• Periodically re-validate spreadsheets. This should include verification of cell formulas and a manual reverification of spreadsheet calculations.