Validating Laboratory Calculation Spreadsheets

A spreadsheet that calculates a laboratory result is a computerised system, and it is validated by showing, before use and after every change, that its formulas give the right answer across the whole range of inputs it will meet, including inputs it should refuse. The protocol is: write the requirements, build with locked formulas and checked inputs, verify against independent hand calculations covering nominal, boundary and invalid cases, release a controlled version, and review it periodically. Annex 11 expects the application to be validated and the extent of validation to follow a documented risk assessment 2.

The validation of a method says the measurement is fit for purpose; it does not say the arithmetic that follows is right. The pages on uncertainty and on stock solutions cover the calculations a laboratory types into a sheet. This page covers the tool. The worked example is a peptide content calculation for an invented peptide, with illustrative inputs; it describes no real material.

Why a calculation spreadsheet is a computerised system

FDA's data-integrity guidance includes, in its description of a dynamic record, one that lets the user modify formulas or entries in a spreadsheet used to compute test results 3. A dynamic record is easy to change and easy to change silently, which is the reason for control. FDA also separates the validation of a platform from the validation of a workflow: qualifying a system does not demonstrate that a particular record generated by it contains the correct calculations, and the workflow must be validated for its intended use, to an extent that fits the risk 3.

Spreadsheets are also error-prone in ordinary use. Panko's review of field audits and experiments reports that errors in spreadsheets are common and that people are poor at finding their own 4. A formula shifted by one row, a range that stops one cell short, a hard-coded value typed over a formula, a unit conversion applied twice: none is visible in the result, and none is caught by a sheet that looks tidy. The MHRA notes that simple systems with no configurable software may need only calibration, while complex systems need validation for intended purpose and that apparent simplicity should not be overlooked 6. A workbook with user-written formulas is configured software, not an instrument.

Requirements: inputs, units, formulas, outputs, limits

Annex 11 asks that user requirements describe the required functions and be traceable through the life cycle 2. For a spreadsheet the requirement is short but must be written before the build. It names the purpose, the inputs with their units and permitted ranges, the formulas with a reference to their source, the outputs with their rounding rule, and the limits that must trigger a warning.

Design controls: locked cells, protected sheets, input validation

1. Separate the sheet into regions: inputs, constants, calculations and results. Shade only the input cells.
2. Put constants (molar masses, reference values, limits) in a labelled block on the sheet, not inside formulas. A number typed into a formula is hidden.
3. Apply data validation to each input: numeric only, within the permitted range, with a message that says why it was refused.
4. Write each formula once and copy it by fill; where one differs from its neighbours, document why.
5. Lock all formula cells and protect the sheet and the workbook structure with a password held by the system owner, not shared with analysts.
6. Stop errors from showing as blanks or zeros. Use explicit tests so that a division by zero or empty input returns a visible message, not a number.
7. Show the version, the file name and the date and time in the header and footer of the printout, so that a printed result can be traced to its file.
8. Keep the master in a controlled folder with write access limited to the system owner. Analysts work from a copy opened from a template, not from the master.

Verification: hand calculation, boundary and invalid inputs

Annex 11 expects evidence of appropriate test methods and scenarios, with system parameter limits, data limits and error handling considered 2. For a calculation sheet that translates into three kinds of test. Nominal cases check that typical inputs give the right answer. Boundary cases check the edges of each permitted range. Invalid cases check that the sheet refuses input it should refuse, and refuses it visibly.

The expected result of every test is calculated independently before the sheet is run: by hand with a calculator, or in a different tool, by a person who did not build the sheet. A test whose expected value was obtained by copying the sheet's formula tests nothing. First check the formula cells by inspection: print the formulas, compare each against the written requirement, and mark each cell reviewed.

Test-case table and acceptance criteria

The acceptance criterion for a nominal or boundary calculation is that the sheet's result equals the independent result when both are rounded to the reporting place, and agrees to at least six significant figures before rounding. For an invalid input the criterion is that the sheet shows the specified message and displays no numeric result.

Record the sheet version, the file hash, the tester, the date and the actual outputs for every row. If a case fails, correct the sheet, increment the version, and repeat the whole table, not only the failed case: a repair can break something that passed. Retain the executed table with the validation report.

Version control, change control and access

Annex 11 requires that changes to a computerised system, including configuration, be made in a controlled manner under a defined procedure 2. For a workbook this means a version number in the file and the file name, a change log recording what changed, why, who approved it and which tests were repeated, and a rule that the master is edited only through that process. Minor edits to labels do not need a full re-test; any edit to a formula, constant, range, validation rule or protection setting does.

Access follows the same rule as for other systems. Annex 11 expects logical controls to restrict access to authorised persons, and the recording of the creation, change and cancellation of access authorisations 2. In practice: analysts have read and use access to a template, the system owner holds change access, and old versions are archived read-only and marked superseded so that nobody opens one by mistake. FDA's concern about shared accounts applies to the shared drive as much as to the chromatography system 3.

Outputs as records: printouts, signatures, retention

A completed workbook holding real data is a record. FDA's scope guidance for Part 11 says that paper printouts of electronic records can meet predicate rules where they are the records relied upon, and that decisions on validation and audit trails should rest on a documented risk assessment 5. Annex 11 requires that it be possible to obtain clear printed copies of electronically stored data 2. A laboratory working to these expectations prints the completed sheet with its inputs, its results, the version and file name, and the date and user, signs and dates it, and files it with the data set.

The printout fixes the numbers at one moment; the file preserves the ability to see the formulas that produced them. Keep both. Store the completed file read-only, with a hash, in a controlled location for the retention period; if the sheet is later found to be faulty, the stored files tell you which results are affected. An electronic signature, if used, must be permanently linked to the record and carry the time and date 2.

Periodic review and retirement

Annex 11 expects periodic evaluation to confirm that a system remains in a valid state, looking where appropriate at functionality, deviations, incidents, upgrade history and validation status 2. Spreadsheets are exposed to a particular upgrade risk: a new version of the host software can change a function's behaviour or its handling of dates. After any such upgrade, re-run the test table.

GAMP 5 second edition keeps the risk-based approach to computerised systems and asks for critical thinking about the approach in each case, together with proportionate use of supplier and software-category information 1. Commonly, a workbook with user-defined formulas is treated as a configured or custom application, rather than as the standard-software category of the host product; the right category is a judgement made and documented for the case, and the live guide should be read for its current wording.

Validation protocol in outline

1. Requirements: write the purpose, inputs, units and ranges, formulas with their source, outputs and rounding, controls and records, and have them approved.
2. Risk assessment: record how a wrong result would affect decisions, and set the depth of testing to match.
3. Design: build with separated regions, constants in cells, data validation, locked formulas and a version header.
4. Formula check: print the formulas and review each against the requirement; sign the printout.
5. Test cases: define nominal, boundary and invalid cases with independently calculated expected results; execute, record and sign.
6. Protection and access: apply protection, set folder permissions, record the hash of the released file.
7. Release: issue the version number, the validation report and the user instruction; archive earlier versions as superseded.
8. Periodic review: repeat the test table after any change or host-software upgrade, and at a set interval; retire the sheet by recorded decision.

The effort is proportionate to the risk. A sheet that makes a dilution table for bench use is validated more lightly than one that produces a reported content value, and a sheet that is replaced by a validated function in the chromatography data system should be retired and removed from use, not left on the shared drive 23.