Validation spreadsheet

Whenever a standard calculation has to be repeated over a long period of time, the generation of a validated spreadsheet should be considered. Validation of spreadsheets can be time- and resource-intensive. Therefore, it has to be decided if a validated Excel spreadsheet is the solution of choice. Beside the actual validation of each spreadsheet, a procedure has to be established to handle and work with the spreadsheet. GXP-related questions to address include the use of secure data storage locations, missing ER/ES compliance, and audit trail in Excel. These areas are as important as the validation itself and will require resources. 

Since Excel is a powerful tool used widely for many different purposes with many options not all options can be discussed in this chapter. The focus of the chapter is on manually self-created spreadsheets for data calculation and checks against acceptance criteria (logical operations). Excel spreadsheets that are used with other electronic systems for automatic data or information entry, for further operations, or used as a view tool for databases are not within the scope of this chapter. Nevertheless, these types of spreadsheets are viewed as a normal spreadsheet with automatic entry, and validation, including validation of the interface, will be included to cover this item. In this chapter we provide guidance in validation and revalidation of Excel spreadsheets and information about managing validated spreadsheets. 

The security strategy selected has to ensure that cells containing formulas (on purpose, by mistake, or by the auto-save function) of the spreadsheet cannot be overwritten. In today s standard office network environment, in some cases, the network itself is, not validated and does not fulfill the electronic record/electro-nic signature requirement. Therefore, the validated spreadsheets should be stored in a protected drive to which only restricted personnel have access. Furthermore, the server used for storage/handling should be qualified. Figure 18.7 is an example. 

Generate inventory (list) for validated spreadsheets that are in use... 

Vendor assessment (needed only if a vendor delivers a validated spreadsheet)... 

Change of software version(s). Changes in software version(s) require revalidation of the spreadsheet irrespective of whether a new version of Excel or Windows is used. It will be a business decision to stay with the old software version to avoid revalidation and save the resources required. In a large company, this will not be an easy decision. In this case, care should be taken to ensure the use of the appropriate software and spreadsheet version, especially if spreadsheets are used in a shared manner. In the case of a global rollout of new software, revalidation must take place prior to the rollout to ensure the use of validated spreadsheets starting on the day of the rollout. 

Have an appropriate procedure available to validate spreadsheets. 

Validate spreadsheets, access databases, and other COTS. 

Validate spreadsheets if going to alternative solutions validate these, too. 

The two approximately matched blended solutions should be run alternately, so that the spiked unknown sample is bracketed on either side by the spiked natural standard. The run sequence for this determination is illustrated in Figure 7. This data should then be processed on a validated spreadsheet to give the final... 

Spreadsheet Analysis Once validation is complete, prescreening the measurements using the process constraints as the comparison statistic is particularly usenil. This is the first step in the global test discussed in the rectification section. Also, an initial adjustment in component flows will provide the initial point for reconciliation. Therefore, the goals of this prescreening are to ... 

The second section of the spreadsheet contains the overall flows, the calculated component flows, and the material balance closure of each. The weighted nonclosure can be calculated using the random error calculated above, and a constraint test can be done with each component constraint if desired. Whether the measurement test is done or not, the nonclosure of the material balance for each component gives an indication of the validity of the overall flows and the compositions. If particiilar components are found to have significant constraint error, discussions with laboratory personnel about sampling and analysis and with instrument personnel about flow-measurement errors can take place before any extensive computations begin. 

Validation also applies to software. In a simple example, you could create an Excel spreadsheet template with fixed formulae to calculate the mean and standard deviation of a range of data. To validate this template you would enter a set of sample data and verify the template-calculated results against the manually calculated results. In order to be confident that the template could be used for further data sets you would password-protect the cell formulae and verify that they cannot be altered without it. 

All formulas are to be written out by hand with the specific numbers in the right places on a sheet provided for the purpose, and the calculations are to be done by calculator. Caution Write the numbers exactly as printed, do not round any digits, or the quality assurance unit (a sort of corporate vice squad) will not approve the report out of fear that someone could have cheated. A validated program can be used. While an Excel spreadsheet as such needs no validation, a simple cell-formula calls for extensive tests and documentation and proof that the sheet is password protected against fraudulent manipulation. On top of that, the analyst s supervisor is required to confirm the calculation and sign off on... 

It is important to keep in mind that the Mathcad spreadsheet I mentioned had by then been very successfully validated (in excruciating detail) over several of their previous product families, and was consistently giving agreement with bench results to within 1% accuracy... 

And finally, I add, my program is a whole lot more than just a spreadsheet. To use it as such is to play Chopsticks upon the mighty organ of a grand cathedral to ask Sean Connery to advertise socks. In other words, the spec from my point of view is partial, expressing the requirements of only one class of user so the implementation classes you see here are chosen to suit a much broader scheme. Nevertheless, it is able to function merely as a spreadsheet when required, and I wish it to be validated as such against the spec.4... 

A historical control database can take on many formats, from a simple spreadsheet (e.g., Microsoft Excel) to a fully searchable database that is interfaced or a part of the laboratory s computer data collection system. Most laboratories that conduct large numbers of studies according to GLP standards have a validated computer data collection system, and some of these systems automatically compile control data ftom studies so the user does not have to reenter the data into a separate historical control database. However, because of the inflexibility of the data acquisition systems, many laboratories still compile their historical control data by manually entering into a stand-alone database, such as customized spreadsheet format (e.g., Microsoft Excel). 

It is important to note that, although the tables of experimental designs in this chapter show an orderly progression of experimental conditions, when the experiments are done, the order must be randomized. A random order can be generated in a spreadsheet, or if statistical or validation software is used, the program might randomize the order. Randomizing the order of experiments is the best way to confound uncontrolled effects and should always be employed. 

Huber has published primers with detailed information on the validation of macros and spreadsheet applications [11] and on the qualification of network infrastructure and the validation of networked systems [12]. 

Validation of software loaded on a computer that is used to control the equipment, to acquire analytical raw data, to process the data, and to print and store the data. Software typically includes operating systems, standard applications software, and software written by a specific user, such as macros or spreadsheet calculations. 

---