Templates for generating Levey-Jennings and Westgard QC charts.
Table 1 User-editable cells Template File Name Cell Contents LJAACC.WK1(1) WGAACC.WK1(2) Test B1 B1 Method G1 B2 Instrument H2 H1 Units P1 F4 Control Name R2 H2 Lot Numbers R4, R23, R42 S5, S33 Mean & SD, level 1 B3, B4 B4, B5 mean & SD, level 2 B22, B23 B32, B33 Mean & SD, level 3 B41, B42 Control data input, level 1 AS5..AS104 BB5..BB104 Control data input, level 2 AT5..AT104 BC5..BC104 Control data input, level 3 AU5..AU104 BD5..BD104 Cumulative Data Range W4 V5 Current Data Range D4 D5 1 QUATTRO|R~ PRO file name: LJQPRO.WQ1 2 QUATTRO|R~ PRO file name: WGQPRO.WQ1
These templates do not make use of the /Graph or :Graph functions of Lotus 1-2-3 (Lotus Development Corp., Cambridge, Mass.). Thus a separate graph file (*.PIC) is not required, thereby saving the time it would take to construct a graph and save it to disk as well as disk space to store it. The QC charts are printed directly from the worksheet session (not from PrintGraph) as a range of spreadsheet cells (/ppr). This capability drastically reduces printing time because the printer is printing text rather than graphics. Matching log sheets, which are automatically updated as QC chart is edited, may also be printed from the spreadsheet session. The procedure thus emulates a word processing function and saves the time required to switch to a separate software application to construct the log sheets.
The QC charts and log sheets are then used to plot a log laboratory QC data. An additional feature is the ability of the templates to calculate the mean, standard deviation (SD), and coefficient of variation (CV) of up to 100 data points on one to three levels of control.
* Methods. Grids were constructed in the 1-2-3 worksheet session by storing a string of keyboard symbols (such as =, -, or *) in consecutive horizontal cells (rows) and a broken vertical line keyboard symbol in consecutive vertical cells (columns). Three grids were placed in the spreadsheet for the three-level Levey-Jennings (LJ) chart template and two grids for the two-level Westgard (WG) chart template. The width of the horizontal cells was edited to 3 from the default width of 9 so that the 31-cell grid would fit on a standard 8.5-inch page with the printer pitch set at 20 characters per inch (cpi).
To facilitate automatic recalculation of the values associated with the grid, formulas are stored in cells adjacent to the grids. For example, the Day or Run numbers appearing above and below the 2 SD line (LJ chart) or 3 SD line (WG chart) are the results of formulas keyed to the first Day or Run number on the upper x-axis of the first grid. The template automatically recalculates the entire upper and lower x-axis for all grids when the value for that first Day 1 or Run 1 is edited. This feature is especially useful in creating WG charts where the first chart of a sequence depicts run 1 through 31, the second chart shows runs 32 through 62, and so on, or with LJ charts when the starting day is other than the first day of the month.
A similar technique was used to place the values along the y-axis. The user-editable mean and SD values stored in designated cells above each grid (i.e., the cell to the right of "Mean =" and "S.D. ="), however, are variables in formulas placed along the y-axis. The addresses of these cells are given in Table 1.
In the LJ chart templates, for example TABULAR DATA OMITTED, the cell address of the level 1 control mean is B3; the SD is in B4. The values for mean and SD in these cells may be edited either directly, by typing in the desired values, or indirectly, by invoking macros that retrieve the mean and SD from the remote area of the spreadsheet where they are calculated and entering them in these cells. The \x macro controls the top grid, the \y macro the middle grid, and the \z macro the bottom grid. Because the Westgard charts have only two grids, only the \x and \y macros are used. When the mean and SD values are edited, either directly or indirectly, the y-axis of the associated grid is automatically recalculated. Thus, by using keyboard symbols that are stored in cells as labels to "draw" the grids, and by associating formulas with the grids to assign values to the x-axis and y-axis, the user can obtain a chart without using Lotus's /Graph features.
Space is provided for the user to type text into vacant cells adjacent to titles. The name of the test, for example, is typed in cell B1 next to the title Test: located in cell A1. When the test name is edited in cell B1, it is automatically updated in the log sheet title. The same principle holds true for the instrument name, method name, units, control name, control lot number, and Day or Run numbers.
Also embedded in the spreadsheet template for the Westgard charts is an interpretation table for the Westgard rules.(1) Like the log sheet, it was included to obviate loading a word processing program in order to print it. The table is used in our laboratory as a form for recording the technologist's decision to accept or reject a run and, in the latter case, as a checklist for recording the control rule or rules violated that led to the decision. The table and the log sheet are entirely independent features of the templates that the user may erase without damaging other functions.
There are spreadsheet templates for both Lotus 1-2-3 and Quattro Pro (Borland International, Inc., Scotts Valley, Calif.). The Lotus templates were written in version 2.01 and the Quattro Pro in version 1.01 but are regularly run in some of the higher versions of each and seem entirely compatible. There are separate templates for the Levey--Jennings and Westgard charts. The file names are as follows: for Lotus, LJAACC.WK1 and WGAACC.WK1; for Quattro Pro, LJQRPO.WQ1 and WGQPRO.WQ1.
* Procedure. Enter the spreadsheet session in the usual manner and retrieve the template file appropriate to your needs. Edit the cells as needed for test, method, instrument, units, control name, and lot numbers. The cell locations are listed in Table 1.
Either calculate the mean and standard deviation in the spreadsheet template or enter values obtained by some other means. If the latter option is selected, the user need only type the externally derived mean and SD in the appropriate cell locations, given in Table 1. This option might be an attractive alternative for users whose instruments calculate TABULAR DATA OMITTED statistical parameters on controls run on the instrument. The user obtains this information from the instrument's QC files, retrieves the appropriate template file, edits the titles, and edits the mean and SD cells that recalculate the y-axis of each grid. For example, in the LJ templates, the mean for the first level of control is typed into cell B3 and the SD into cell B4. The template will recalculate the y-axis of the top grid as these are edited. The same process is used for the other levels of control but on the middle grid (cells B22 and B23) and bottom grid (cells B41 and B42).
If calculation of the mean, SD, and CV is desired, enter the data for the first level in the range of cells listed in Table 1. For example, the data for the level 1 control goes in cell range AS5 to AS104 (that is, AS5..AS104) of the LJ chart templates. The mean, SD, CV, number (N) of observations, and maximum and minimum values are located in cells 106 through 111 of the same column and are updated as each value is keyed in. For levels two and three, enter the data in the range of cells indicated in Table 1. Invoke the appropriate macro to stuff the calculated mean and SD in the appropriate cells resulting in the recalculation of the grid. Example: To invoke the \x macro, which controls the top grid, hold down the <Alt> key and press the <x> key. Table 2 describes the macros in the LJ chart template. Table 3 lists macros for the WG chart template.
To have the template create a text string of the calculated N, mean, SD, and CV so that they will appear as text on the printed QC chart above the appropriate grid, invoke the \j macro. This information could instead be typed into the appropriate cell as a label--a cumbersome and time-consuming task. The \j macro is useful when the user desires to have the exact calculated values appear on the chart but wishes to use rounded values in the cells that control the calculation of the y-axis. This is important when the calculated SD is not large enough to yield a usable difference between the upper and lower limit of the calculated range. In our laboratory, BUN frequently falls into this category: The instrument reports results in whole numbers, but the SD on the lowest level of control is often a number such as 0.156, which would yield a range covering 0.624 mg/dl, constituting less than one reporting unit. To compensate, we round the mean to the nearest whole number and the SD up to 0.5 mg/dl. These values are entered in the cells that calculate the y-axis. The \j macro is invoked to display the actual calculated mean and SD.
To change the starting day or run number along the x-axis, edit only the first day or run number above the first grid. This is done in the LJ chart templates by editing cell C6. In the WG chart templates, cell C8 is edited. If you want the date range over which the data used to calculate the mean and SD was gathered to appear on the QC chart, edit that information in the appropriate cell above the first grid. Move the cursor to that cell address, press <F2>, type the changes, and press <Enter>. To copy this date information to the other grid or grids, invoke the \d macro. Follow the same procedure for a cumulative date range, but invoke the \c macro.
Invoking the \p macro prints the QC chart. First, however, set the printer parameters, such as pitch and font. The pitch must be set to 20 cpi if the grids are to fit on standard, letter-size paper in the portrait format. For printers unable to achieve 20 cpi, rotate the image 90 degrees by setting the printer to use the landscape format. The chart in Figure 1 was printed on a laser printer at 17 cpi. Similarly, print the log sheet by invoking the \l macro and the Westgard interpretation table with the \t macro. The log sheets may be printed at a pitch of 12 cpi. Save the file in the usual manner: by typing /fs.
If care is taken, unused portions of the template may be erased to save disk space. If additional grids are needed in a file, copy existing cell ranges and edit the formulas as needed. The macros and formulas are viewable and editable by the user. The authors encourage users to experiment and adapt the templates to their needs.
* Versatility. The utility of these spreadsheet templates is varied. They may be helpful in the large laboratory with on-line QC programs in a mainframe computer system but needing backup QC charts for periods of down time. Similarly, the templates will assist users in the small laboratory who need the capability to calculate mean, SD, and CV and prepare QC charts from the resulting data. The added speed of the pseudo-graphics and the combined spreadsheet and word processing functions enable these spreadsheet templates to generate large numbers of QC charts and log sheets in a relatively short time.
A copy of these templates and a detailed set of instructions may be obtained at no charge by sending a DOS-formatted diskette in a self-addressed diskette mailer (with return postage affixed) to: Carole Smith, Laboratory Service 113, VA Medical Center, 1310 24th Ave. South, Nashville, TN 37212.
Alternatively, the program is available at no charge on Med TechNet, a clinical laboratory-oriented computerized bulletin board operated through Western New York Microcomputer, Inc., East Amherst, N.Y. The modem number is (716) 688-1552. Set your communications program to 8 data bits, 1 stop bit, no parity. Request the text file named LJWG.ZIP. The program's templates and an abstract of the text of this article can be found in the file named LJWGABST.TXT
Smith is supervisor of clinical chemistry, Dr. Sephel is director of clinical chemistry, Dr. Woodward is chief of laboratory service, and Duncan is computer liaison, surgical service, VA Medical Center, Nashville, Tenn.
1. Westgard JO, Klee GG. Quality assurance. In: Textbook of Clinical Chemistry. Philadelphia, Pa: WB Saunders; 1986: 440-442.