Use of a spreadsheet for method evaluation statistics

Medical Laboratory Observer, Oct, 1988 by Michael W. Sullivan

At the time this article was written, the author was an instructor in the Clinical Laboratory Science Division of the University of Alabama at Birmingham He is now a medical student al the University of Cincinnati. Many types of statistics must be generated in the assessment of a laboratory instrument or method. If a lab has spreadsheet software, it can handle such tasks with relative ease.

Fairly complicated formulas can be input into the cells of the spreadsheet, eliminating the need for expensive statistical software. All that is required is a little time and imagination, and a basic understanding of statistical calculations and spreadsheet programming.

For this purpose, Appleworks is a good integrated software package, combining a word processor, spreadsheet, and database. (The approaches described will work with other spreadsheets and with microcomputers other than Apples, although the spreadsheet formulas and commands may differ.) The value of using integrated software is that information from the database and spreadsheet can be transferred to a word processing document and inserted wherever one desires. This facilitates the organization and writing of a report. If the integrated software also has graphics capabilities, an entire report can be assembled using one piece of software.

Once a spreadsheet template is programmed with formulas for each cell and the desired statistical tables, all that is required is insertion of the raw data. The data can be inserted by keyboard or stored in a separate spreadsheet file and moved to the spreadsheet's statistics template when needed.

After the data have been entered into the appropriate spreadsheet columns, open-apple-K (a two-key command on Apple microcomputers) is pressed five times to complete the calculations. The statistics thus generated are automatically entered into tables in the spreadsheet, which makes them read for printing. The design of the tables provides an organized format for statistical presentation.

I would like to describe a template I developed for comparison of laboratory methods. Using patient values, the template calculates the mean, variance, and standard deviation for each method along with simple linear regression statistics. This information is arranged in tabular form (Figure 1).

The basic design for data entry for this template is taken from the National Committee for Clinical Laboratory Standards' proposed guideline EP9-P..sup.1 If duplicate tests are pert-ormed on a specimen, both results can be entered directly into the template, and the mean ot' the results is calculated. The template is set up to accept 40 duplicates for both the method under consideration and comparative methods. In cases where fewer than 40 specimens are tested, (a IF statements (Apple commands specifying what is to bedone it' something is the case) are used in some of the formulas so that statistical operations will be performed only on the actual number ot' data points used.

For each test method, the run sum (the sum of all the means of the duplicates), count (the number of analyses), grand mean, variance, and standard deviation are calculated and displayed in a table next to thc raw data. See Figure 1.

The sum of the means of the duplicates and the number of analyses for each method are calculated using (a SUM and (a COUNT commands, respectively. Only those spreadsheet cells with values will be summed or counted. The grand mean is calculated by dividing the count into the sum of the means.

Calculation of the variance is based on the formula [Sigma](X - -/X).sup.2 [devided] N - 1, which subtracts the grand mean from each mean of the duplicates, squares the result, sums the squares, and divides the total by the number of analyses minus 1. This formula goes into all cells that may contain test data.

Assuming the test data cells are identified as AA 1 5 through AA54 in the spreadsheet, and the count is contained in cell N15, the formula in cell P15 for the variance is (@ SUM(AA)5...AA54)/(N151), as shown in Figure 11. Q15 is the standard deviation cell in Figure 11. There, the formula (@ SQRT(P15) tells the spreadsheet to take the square root of the value in cell P15 (the variance) and to insert it into Q15.

These calculations are performed for each test method. The table with the sum, count, mean, variance, and standard deviation can then be printed to the word processing document.

With this template, the follow ing statistics are also generated: correlation coefficient, slope, yintercept, coefficient of determination, coefficient of non-deter mination, residual variance, tan dard error of the regression line, standard eff or of the slope, dard error of the y-intercept, and the 95 per cent confidence interval for the standard error of the last two statistics.

I have also set up templates for calculation of between-run and within-run variance using another NCCLS document, tentative guideline EP5-T. 2 Many other templates can be created, even for calculating non-parametric statistics, if one knows the formulas that are necessary and how to organize the spreadsheet. The NCCLS guidelines have tables and work sheets that are readily convertible to a spreadsheet for calculation of many kinds of statistics.