Tests, spreadsheet

I work through the example presented in figure 8.7 here. Spreadsheet 8.1 shows the data with four measurements made at each of eight concentrations. The likely linear range is shown in the figure, and to illustrate the method, the range with one more point is tested first (to show it is not sufficiently linear) and then the more likely range is tested. Spreadsheet 8.2 shows the calculations for the points up to 50 nM. 

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 example spreadsheet covers a three-day test. Tests over a period of days provide an opportunity to ensure that the tower operated at steady state for a period of time. Three sets of compositions were measured, recorded, normalized, and averaged. The daily compositions can be compared graphically to the averages to show drift. Scatter-diagram graphs, such as those in the reconciliation section, are developed for this analysis. If no drift is identified, the scatter in the measurements with time can give an estimate of the random error (measurement and fluc tuations) in the measurements. 

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. 

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... 

Each individual spreadsheet contained several logical tests ensuring that no calculations or results could be reported unless all members of a required raw data set were entered. Control over the use of the spreadsheets was provided by issuing the workbook templates to the laboratories in forms that contained locked and unlocked... 

The word practical in the title describes a characteristic feature of this book. However, it could easily be misunderstood. It is not a book that is meant to be taken into the laboratory to remain on the lab bench next to instruments and test tubes. The book is practical insofar as every bit of theory applicable to data analysis is exemplified in a short program in Matlab or in an Excel spreadsheet. 

Test your IT skills. Try creating Excel spreadsheets based on the Michaelis-Menten equation (Equation 2.9) and its variants (Equations 2.10 and 2.11). Insert into your spreadsheets your own values for JCm, Vmax, [S], [I] and A) and use Excel to plot Michaelis-Menten, Lineweaver-Burke and Eadie-Hofstee graphs. 

Such calculations can give an approximate estimate of the Hkely maximum yield of explosives and explosives mixtures, and may readily be transferred to a spreadsheet for ease of use. In practice, small charges, particularly of improvised explosives, tend to produce less than their maximum output, and this needs to be estabhshed by experimental measurements. The results of simple calculations such as those above can be particularly helpful in the design of suitable experimental tests of explosives. 

Making prototypes and laboratory testing of polymer disks are projects that require limited cost and time duration. Certification by the FDA, on the other hand, is a long drawn out and costly process, where animal tests are followed by three phases of clinical trials, which have been described elsewhere (e.g. Suffness 1995). When the results are assessed and evaluated, a brand new product that costs more than 100 million can also run into many unforeseen problems, which makes many financiers very cautions. One of the biggest unknowns is who would pay for this costly new form of medication, and whether the medical insurance companies and Medicare would approve payment. This is the reason why so many information technology products, such as digital cameras and spreadsheets, are launched quickly, as they require much less capital to start and do not require FDA clearance. 

Due to the limited availability of test materials, the evaluation of biological materials often involves more statistical analysis to extract data than is typically used for the other materials classes. In this exercise, the use of statistics and spreadsheets will be illustrated in order to compare the experimental results of wear data for the candidate materials. 

Using a spreadsheet function or analysis tool, perform a Smdent s f-test to compare the mean values of each of the first four wear rates with that of gold (the last value in the column) at an agreed-upon confidence level—for example, 95% confidence level. If you have not yet learned how to do Student s f-test, check out any book on elementary statistics, and learn how to do a f-test, or use the Help menus in your spreadsheet package. The goal is to determine if the mean wear rates of the four ceramic materials are statistically different from the wear rate for gold. If done correctly, you will have performed four separate f-tests in this step. 

Spreadsheet 2.2. Example of calculations for a Rankit plot to test normality. 

Spreadsheet 2.3. Cochran test for homogeneity of variances in the soil analysis example given in the text. 

Spreadsheet 2.4. (a) The Add-In menu, (b) The Data Analysis Tools menu showing the three t test options, (cj Menu for the t test assuming equal variances. 

Spreadsheet 8.2. Calculations for test of linear range of data shown in spreadsheet 8.1. Range tested, 3.1-50.0 nM N=24 data points number of concentrations, k, = 6. ME = measurement error, LOF = lack of fit, SS = sum of squares, MS = mean square. 

The tests should be repeated thereafter at regular or nonregular intervals. Therefore, any test case should be designed for reuse using sets of data inputs and known outputs. If the program is used infrequently, it is good practice to run the tests each time the program is used. Spreadsheet validation is discussed in detail in Chapter 18 and in Ref. [11]. 

Validation of spreadsheets should be differentiated in separate sections or documents. These sections or documents should be described in the validation plan and will include the following steps. Some of the steps are used for documentation of the status of the spreadsheet, while others deal with the testing of the spreadsheet. The following 10 items will be differentiated in the validation of spreadsheets. 

Names of user(s) who developed the spreadsheet and the validation tests... 

An example of a validation plan, including related information, is given in Figure 18.2. This example is used in all individual sections following it to explain what is needed in the various sections. The example is based on a combination of spreadsheets for calculation of content uniformity by UV analysis developed for a development area. The spreadsheet is developed so that it is flexible and can be used for different products. Furthermore, it checks acceptance criteria set for calibration standards and a system suitability test. 

The requirement section has to describe the requirements of the spreadsheet clearly. The requirements have to be chosen in a manner that permits tests to be developed and performed to check if the spreadsheet fulfills its functions. For each requirement a corresponding test should be performed. The requirement section will include information about purpose, functional, performance, security, and design requirements. 

The spreadsheet will be used in department/group/area ABC. The spreadsheet will be used after successful validation (or the spreadsheet is in use since [DATE], if validation is performed retrospectively). John Doe developed the spreadsheet. Jim Doe will perform the validation. Validation will include items... [define which sections (e.g., requirements, design, tests) will be applicable to the validation]. 

The design section will include information about purpose, system and functional overview, data flow, data sheet formats, and test strategy. The section describes how the requirements are addressed in the spreadsheet. The main part of the design documentation contains a printout of the spreadsheet in the formula view, with line and column headers. The purpose of the individual formulas and/or macro must be described, including the sequence in which the calculation will be performed. In addition, the following information should be provided ... 

Testing will include all types of cells mentioned in (a) to (c). All cells that will not be used for data entry are protected. A printout of the spreadsheets documenting the protected cells will be provided. 

The test chapter section will include information about purpose, test documentation, review and approval, formal test plan, and test execution. Testing the spreadsheet should take all requirements into account. Tests using known cases and data including known results have to be developed. The data set used for testing should include representative data. If applicable, tests should also include data sets that will test values that exceed the anticipated range of normal operations and check for data close to the acceptance criteria limits. Data that make no sense (text in a numeric field) or are not allowed to be used in the calculation should be considered in the testing phase as well. The simplest test to validate a spreadsheet calculation is to perform a manual calculation and document it. All tests performed, results expected, acceptance criteria for the tests, and procedures for alternative calculation (e.g., manual calculation) will have to be documented. Persons who perform the testing have to be trained appropriately and must be mentioned in the documentation. 

For a check of the spreadsheets, test data were used. The test data represent values close to the acceptance criteria, which are within and outside the acceptance range. The following checks will be performed ... 

Testing must be documented according to cGXP requirements. The results have to be reviewed and approved. Documentation of the entire testing procedure should have a printout of all results obtained with the spreadsheet as well as results obtained with the alternative calculation. Figure 18.6 is an example. 

---