# Volume III - 4.5 Development and Validation of Spreadsheets for Calculation of Data

Orientation and Training

DOCUMENT NO.:
III-04
VERSION NO.: 1.4
Section 4 - Basic Statistics and Presentation
EFFECTIVE DATE:
10-01-03
REVISED:
1-31-13

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.

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.

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.