Do You konw the How to Validate the Excel Sheets used in the Quality control for statstical data Calculation

 

DEVELOPMENT PROCESS OF EXCEL SHEETS

User Requirements

Before the spreadsheet is developed, the users should define what they expect the spreadsheet to do, which calculations have to be performed, the layout, etc. This is defined in the User Requirement Specifications (URS), which would also include the Functional Specifications. For example, a (non-exhaustive) URS for the gravimetric pipette control spreadsheet may look like this:

URS-1.      The spreadsheet should be applicable for volumes from 0.01 to 10 mL URS-2.         The spreadsheet should only accept a sample number of 10

URS-3.      The spreadsheet should calculate the mean, standard deviation (SD) and relative SD (RSD=(SD/average)x100%) of the 10 samples

URS-4.      The spreadsheet should use the density of water between 20°C and 25°C in steps of 1°C

URS-5.      The Inventory Number of the pipette should be visible on the screen and on the print- out.

URS-6.      The spreadsheet should indicate whether the accuracy and RSD fulfil the acceptance criteria according to the SOP

URS-7.      The spreadsheet input should only allow numerical values for the nominal volume of the pipette, sample weight and temperature

 Requirements concerning security, protection and GLP compliance have also to be taken into account:

URS-8.      The calculations in the spreadsheet should be protected against unauthorized modifications

URS-9.      After completion of the measurement, the spreadsheet should be initiated and dated electronically by the user

URS-10.    Making changes to the spreadsheet (modification/deletion) after signing off (initials/date) should not be possible

URS-11.    The spreadsheet template and completed spreadsheets should be retained in a secure location in an electronic form with access control

Spreadsheet Design

The spreadsheet design corresponds to the system architecture of a software program, and a good design will:

·       reduce the number of errors during programming,

·       make it easier to maintain, adapt or further develop the spreadsheet and

·       make it easier to verify the function during validation.

To illustrate a structured layout, the sample spreadsheet shown in Figure 1 is divided into an area where data input occurs (columns A and B: inventory number, nominal value, sample weight, water temperature), an area where the results are calculated and presented (columns D–F: mean, SD, RSD, etc.) and an area containing constants and criteria (columns H–L: water density at different temperatures, maximum Delta and RSD). The different areas may be highlighted by different colours or shadings

 

The spreadsheet is password-protected, and data can only be entered in the marked cells (column B). Data entry is secured by error messages and ranges.

It is important for the design of a spreadsheet that:

·       input and output areas are separated;

·       calculations, constants and parameters cannot be changed by the user (locked cells);

·       a blank template is used – the use of “master copies” with typical data should be avoided; and

·       the template is password-protected (at sheet, workbook and VBA code level).

At this stage, it should be defined where the productive template is stored. The file paths for automatic opening or saving of files and the security settings should be adapted accordingly.

Such a design provides the basis for straightforward validation. All input data, constants and calculation results are shown on the spreadsheet, which allows stepwise verification, e.g. with a calculator. All the information required to verify the calculation is a printed hard copy or a corresponding screenshot

The spreadsheet is password-protected, and data can only be entered in the marked cells (column B). Data entry is secured by error messages and ranges.

It is important for the design of a spreadsheet that:

·       input and output areas are separated;

·       calculations, constants and parameters cannot be changed by the user (locked cells);

·       a blank template is used – the use of “master copies” with typical data should be avoided; and

·       the template is password-protected (at sheet, workbook and VBA code level).

At this stage, it should be defined where the productive template is stored. The file paths for automatic opening or saving of files and the security settings should be adapted accordingly.

Such a design provides the basis for straightforward validation. All input data, constants and calculation results are shown on the spreadsheet, which allows stepwise verification, e.g. with a calculator. All the information required to verify the calculation is a printed hard copy or a corresponding screenshot

Development Testing

Systematic testing of User Requirement Specifications

At this stage, the developer tests whether the spreadsheet fulfils the URS. A simple set of typical data for the input values is used and varied in order to simulate the situations that are needed to test all URS.

Code review

It is highly recommended that this review should be conducted by two persons – the developer and an experienced reviewer who is familiar with the intended use and the applied functions.

For spreadsheets, the code review consists of checking the content of cells, especially formulas, formula locations, cell numbers and ranges, syntax of additional functionality, and VBA code

Installation Qualification (IQ)

A spreadsheet template is a critical file since it usually contains the complete source code (VBA code and spreadsheet functions) and can be used to execute the program. In terms of software development, the source code and the compiled version are stored in the same file. Therefore, it is important to make sure that the spreadsheet template is stored in a secure, controlled environment. Servers with the appropriate read/write/modify security settings can provide a reasonable level of control and are therefore the recommended location for storage of templates. Alternatively, the use of a DMS comprising tools such as version control and backup function, as well as the possibility of access control can be considered.

It is self-evident that adequate backup measures have to be in place so as to avoid any damage to or loss of the template (e.g. if any of the above measures should fail).

In order to ensure that the spreadsheet can be utilised throughout the test facility, it may be necessary to check that

·       the spreadsheet can be run on different computers (identical or different hardware configurations, user profiles, local settings) and

·       the spreadsheet can be installed and run using different software versions.

Ability to run the spreadsheet on different computers

This is especially important when the spreadsheet is stored as a template on a server and run by different users on their desktop PCs. In many companies, the computers may have identical or similar hardware, but the configuration (properties or settings,

e.g. operating system language) may be different depending on the use of the specific PC. For example, a computer used for instrument control may run with US versions of the software, whereas further data handling may be done in the office using a local version.

Special attention is required if normal use involves automatic access to raw data files (e.g. reading an export file created by an analytical instrument) and/or automatic or manual saving to certain folders on the network.

It is important to test whether a spreadsheet is capable of running on different computer hardware if that is part of its intended use. This would require a test of the influence of user profiles, local settings, etc.

Ability to install and run the spreadsheet using different software versions

It is important to test the spreadsheet on different software configurations if they are actually used in parallel. This does not need to be tested during installation qualification if it can be ensured that identical versions of the operating system and spreadsheet software will be used.

 

Operational Qualification (OQ)

Systematic tests of the initial user requirements have been performed during the development testing phase (usually by the software developer). It is not necessary to repeat every single test in this phase, but typical cases with all the basic functions should be included. It is recommended that all tests should be performed with different and independent data sets.

Test for ruggedness

Choose input values that lead to unacceptable or impossible results. Try to enter data in incorrect formats or in the wrong cells (text instead of numbers, values out of range, wrong number of decimal places, or real numbers instead of integers; see Table 1). In these cases, the user requirements should already specify what behaviour is required and expected from the system. As many of these tests have already been included in development testing, the focus should be on issues arising from the productive environment at that stage. This may include repeated tests with real data in order to discover malfunctions due to loops, or values that are not correctly re-set at the end of the execution. Testing of security aspects should also be included at this stage since the spreadsheet is now installed in the productive environment.

Table 1: Examples of tests to evaluate the ruggedness and security aspects of the spreadsheet

 

Test relating to URS:	Test	Test result/acceptance criteria
...
URS-7	Enter “q” as weight in the sample 1 field.	The error message “Please enter a weight in grams!” appears.
URS-7	Enter “q” as temperature in the temperature field.	The error message “Please enter a temperature between 20 and 25°C!” appears.
...
URS-8	Place the cursor in cell E4 and press “Delete” to erase the content of the cell.	The error message “The cell or chart you are trying to change is protected and therefore read-only” appears.
...
URS-9	Enter “A1” into the Pipette (Inventory Number) field and press the “Enter/Save” button.	The error message “You have to fill-out all the green input cells!” appears.
...

 

8.3             Performance Qualification (PQ)

Table 2: Examples of tests with selected input data

 

Test relating to URS:	Test	Test result/acceptance criteria
...
URS-3,	Enter “A1” in the Pipette (Inventory Number)	Results: mean 10 g, SD 0.015, RSD
URS-6	field, “10” for the Nominal Value (ml) and	% 0.15, Accuracy ok, RSD ok
	“22” for the Temperature (°C).
	Enter the following values for samples 1–10:
	9.98, 9.98, 9.99, 9.99, 10, 10, 10.01, 10.01,
	10.02, 10.02
URS-3,	Now enter the following values for samples	Results: mean 10.078 g, SD 0.173,
URS-6	1–10:	RSD % 1.717, Accuracy ok, RSD out
	9.95, 9.95, 9.99, 9.99, 10, 10, 10.05, 10.05,	of spec
	10.4, 10.4
URS-3,	Now enter the following values for samples	Results: mean 10.35 g, SD 0.053,
URS-6	1–10:	RSD % 0.512, Accuracy out of spec,
	10.3, 10.3, 10.3, 10.3, 10.3, 10.4, 10.4,	RSD ok
	10.4, 10.4, 10.4
URS-3,	Now enter the following values for samples	Results: mean 10.00 g, SD 0.000,
URS-6	1–10:	RSD % 0.000, Accuracy ok, RSD ok
	10.0, 10.0, 10.0, 10.0, 10.0, 10.0, 10.0,
	10.0, 10.0, 10.0
...

 Test with selected input data

The goal of this step is to verify all possible and all special cases (including boundary values), even if they are very rare, pursuing a systematic approach (see Table 2). In contrast, the real data will typically be of relatively high consistency and cover only realistic cases, not necessarily the extremes. Therefore, the input data has to be selected to cover all functional requirements and combinations of theoretical cases for decision criteria (branching, conditional instructions), as well as all extreme values or special data points.

Test with real data

From the user’s point of view, this is the principal part of the validation process – applying real measurement data as input data. Data input can be done manually or by importing a file, depending on the intended use of the spreadsheet. Irrespective of whether this is a manual or an automated step, real data from previous measurements is to be used. It is important to use a sufficient number of different real data sets in order to obtain a high level of confidence that the spreadsheet functions as intended.