# Using spreadsheet macros to minimize labor-intensive analysis.

Clinical laboratories occasionally have the need to compare the
performance of different instruments or methodologies for a given
analyte. Even when this involves only a single analyte, the task is
quite time-consuming. When a major analyzer is being replaced, and
perhaps 30 analytes are involved, the task is horrendous.

I have been involved in these procedures a number of times in my career and before I developed an analysis procedure using computer spreadsheets, the process took several weeks to complete. Now it takes a matter of hours.

Using an IBM desk-top personal computer and Lotus 1-2-3 software from the Lotus Development Corp., Cambridge, Mass., version 2.0 or later, I developed macro commands that reduce the process of analyzing linear regressions . I have also used this procedure for looking at reference labs' turnaround times and for comparing different analyzers and sets of numbers.

A spreadsheet is essentially an electronic version of an accountant's data pad. Numerical operands or text can be entered into individual cells directly, or through the use of formulas and special functions such as macros. A macro is by far the easiest and fastest way to analyze data, once it is written.

I will be describing, in detail, two macros I have written and a number of manual commands necessary to complete the regression-line data analysis. I have assumed the reader has a minimal understanding of 1-2-3. Basic functions are well explained in the manual and should not present any difficulty to the user. (Any PC-compatible computer or spreadsheet software may be used, but the manuals may need to be consulted if some key assignments differ for non-IBM machines.) A graphics printer and monitor are also needed.

The first step in designing the regression analysis worksheet is to enter the macro commands that simplify a series of complicated or repeated instructions to a single key stroke.

In our case, the macros used for analyzing instrument performance are listed in Figure I and can be entered in either the upper left- or lower right-hand corner of the spreadsheet. The accompanying comments are for reference information only and do not have to be included.

When defining a macro, cells that begin with a number or a slash must be preceded by an apostrophe (') to prevent the command from functioning as a mathematical operation. For example, cells that begin with a number, a slash, or backslash, should be receded by an apostrophe to indicate text entries.

When identifying the macro, I prefer to use letters that reflect the tasks. These are inserted in column one and the sequence of commands assigned to that keystroke should be inserted in the column to the right. After the commands for the macro are entered, each macro must be assigned a name before it will be functional.

To do this, place the cursor on the cell name of the macro. Any number or letter except 0 can be used. Now assign that name to the cell by successively calling the menu commands: Range, Name, Label, Right (/RNLR), and Enter. Lotus 1-2-3 has now assigned the name /r to the instructions in the column to the right. This principle is repeated when defining the graph-drawing macro, also listed in Figure 1.

The sequence of commands is later initiated by holding the macro function key, ALT on an IBMbased system, and pressing the macro identification key. For example, holding down the ALT key and pressing r would start the regression analysis macro.

I recommend that you save the worksheet after the macros are typed in and named. I load the worksheet from a write-protected disk and then change disks so I don't risk losing the information.

After this setup is completed, it is time to enter data as listed in Figure IIA. The first two columns include the pairs of "Reference Method" data. The third column, "Regression Line," is calculated by invoking the regression macro, ALT r as shown in Figure IIB.

After the macro has been initiated, a prompt sheet may be helpful for the various sequential input. For example, you must first identify the reference data range by placing the cursor on the first data point and highlighting all the entries by pressing the period key (.) and using the down arrow. Again, in the same manner, highlight the data in the subject list.

Next, you must identify the first cell of 10 empty rows and four columns for the regression data display. Position the cursor in the first empty column to the right of the "Regression Line" column, and press Enter. This will display the output listed in Figure IIC. The value labeled "X coefficient" is the slope of the regression line and the value labeled "constant" is the Y-intercept.

The next step involves manual handling of the data and creating a scatter graph. A formula must be created to describe the regression line so it will overlay a scatter graph of the individual data points. The formula is: Y = B + mX, where B is the Y-intercept, m is the slope, and X is the reference data point.

This formula can be entered by placing the cursor in the "Regression Line" column next to the first set of data points, and pressing the + key to initiate the formula. Moving the cursor to the constant value, and pressing F4, the absolute location key, identifies it as the Y-intercept. Now move the cursor to the X-coefficient value and press F4 and * to identify the slope. The cursor has now returned to its orginal position and can be moved to the first reference data point. Pressing Return will finalize the formula.

The calculated value will appear in the "Regression Line" column. You are now ready to copy the formula to the cells below using the copy function key in the menu. The slope and intercept factors w Ill be held constant because they were identified by the absolute value key, F4, and only the reference value will be allowed to change as the formula is copied down the column and the calculated values appear.

Up to this point, the data pairs have been in the order they were entered. They can now be sorted by positioning the cursor on the first value under reference data and executing the menu commands: Database, Sort, Reset Conditions, and Select Data Range (/DSRD). In the same manner as before, define the range by highlighting all three columns of data. The commands A and G, for ascending order and go, complete the sorting procedure. This is much quicker than sorting before entering the data.

Drawing the graph requires a little more keyboard input. Always begin with the cursor at the top of the reference column. The graph-drawing macro has been named Ig, so hold ALT and press G to invoke it.

With the cursor at the top of the range of values that will be plotted on the X axis, highlight the column as before, then repeat the procedure for the Y axis data and the regression line data. These will both be plotted on the Y axis.

Commands in the macros will now pause so graph titles can be added. Use whatever is logical for your particular situation. I used: Regression Analysis: Glucose; Paramax vs. Hitachi 705; Hitachi, mg/dl; Paramax, mg/dl.

Press Return at the end of each line, and then assign legends and symbols for each of the data groups in the same manner. I used Data Pairs and Regression Line.

After the titles have been entered, the graph itself must be saved as a separate file for printing and kept with the worksheet for future reference. It is most convenient to use the same name for both. For this example, I used GLUCOSE for both the graph and file names.

All of the data entry is now complete, and the graph, similar to Figure III, will appear on screen. Pressing any key will remove it, so this is a good point at which to save the worksheet for future use. The section of the worksheet containing your data and the graph should be printed using the Lotus Print Graph program.

Days of analysis are now matters of minutes. The program offers a cost savings for the time saved, but also a comfort savings when you realize you don't have to take it home to finish.

I have been involved in these procedures a number of times in my career and before I developed an analysis procedure using computer spreadsheets, the process took several weeks to complete. Now it takes a matter of hours.

Using an IBM desk-top personal computer and Lotus 1-2-3 software from the Lotus Development Corp., Cambridge, Mass., version 2.0 or later, I developed macro commands that reduce the process of analyzing linear regressions . I have also used this procedure for looking at reference labs' turnaround times and for comparing different analyzers and sets of numbers.

A spreadsheet is essentially an electronic version of an accountant's data pad. Numerical operands or text can be entered into individual cells directly, or through the use of formulas and special functions such as macros. A macro is by far the easiest and fastest way to analyze data, once it is written.

I will be describing, in detail, two macros I have written and a number of manual commands necessary to complete the regression-line data analysis. I have assumed the reader has a minimal understanding of 1-2-3. Basic functions are well explained in the manual and should not present any difficulty to the user. (Any PC-compatible computer or spreadsheet software may be used, but the manuals may need to be consulted if some key assignments differ for non-IBM machines.) A graphics printer and monitor are also needed.

The first step in designing the regression analysis worksheet is to enter the macro commands that simplify a series of complicated or repeated instructions to a single key stroke.

In our case, the macros used for analyzing instrument performance are listed in Figure I and can be entered in either the upper left- or lower right-hand corner of the spreadsheet. The accompanying comments are for reference information only and do not have to be included.

When defining a macro, cells that begin with a number or a slash must be preceded by an apostrophe (') to prevent the command from functioning as a mathematical operation. For example, cells that begin with a number, a slash, or backslash, should be receded by an apostrophe to indicate text entries.

When identifying the macro, I prefer to use letters that reflect the tasks. These are inserted in column one and the sequence of commands assigned to that keystroke should be inserted in the column to the right. After the commands for the macro are entered, each macro must be assigned a name before it will be functional.

To do this, place the cursor on the cell name of the macro. Any number or letter except 0 can be used. Now assign that name to the cell by successively calling the menu commands: Range, Name, Label, Right (/RNLR), and Enter. Lotus 1-2-3 has now assigned the name /r to the instructions in the column to the right. This principle is repeated when defining the graph-drawing macro, also listed in Figure 1.

The sequence of commands is later initiated by holding the macro function key, ALT on an IBMbased system, and pressing the macro identification key. For example, holding down the ALT key and pressing r would start the regression analysis macro.

I recommend that you save the worksheet after the macros are typed in and named. I load the worksheet from a write-protected disk and then change disks so I don't risk losing the information.

After this setup is completed, it is time to enter data as listed in Figure IIA. The first two columns include the pairs of "Reference Method" data. The third column, "Regression Line," is calculated by invoking the regression macro, ALT r as shown in Figure IIB.

After the macro has been initiated, a prompt sheet may be helpful for the various sequential input. For example, you must first identify the reference data range by placing the cursor on the first data point and highlighting all the entries by pressing the period key (.) and using the down arrow. Again, in the same manner, highlight the data in the subject list.

Next, you must identify the first cell of 10 empty rows and four columns for the regression data display. Position the cursor in the first empty column to the right of the "Regression Line" column, and press Enter. This will display the output listed in Figure IIC. The value labeled "X coefficient" is the slope of the regression line and the value labeled "constant" is the Y-intercept.

The next step involves manual handling of the data and creating a scatter graph. A formula must be created to describe the regression line so it will overlay a scatter graph of the individual data points. The formula is: Y = B + mX, where B is the Y-intercept, m is the slope, and X is the reference data point.

This formula can be entered by placing the cursor in the "Regression Line" column next to the first set of data points, and pressing the + key to initiate the formula. Moving the cursor to the constant value, and pressing F4, the absolute location key, identifies it as the Y-intercept. Now move the cursor to the X-coefficient value and press F4 and * to identify the slope. The cursor has now returned to its orginal position and can be moved to the first reference data point. Pressing Return will finalize the formula.

The calculated value will appear in the "Regression Line" column. You are now ready to copy the formula to the cells below using the copy function key in the menu. The slope and intercept factors w Ill be held constant because they were identified by the absolute value key, F4, and only the reference value will be allowed to change as the formula is copied down the column and the calculated values appear.

Up to this point, the data pairs have been in the order they were entered. They can now be sorted by positioning the cursor on the first value under reference data and executing the menu commands: Database, Sort, Reset Conditions, and Select Data Range (/DSRD). In the same manner as before, define the range by highlighting all three columns of data. The commands A and G, for ascending order and go, complete the sorting procedure. This is much quicker than sorting before entering the data.

Drawing the graph requires a little more keyboard input. Always begin with the cursor at the top of the reference column. The graph-drawing macro has been named Ig, so hold ALT and press G to invoke it.

With the cursor at the top of the range of values that will be plotted on the X axis, highlight the column as before, then repeat the procedure for the Y axis data and the regression line data. These will both be plotted on the Y axis.

Commands in the macros will now pause so graph titles can be added. Use whatever is logical for your particular situation. I used: Regression Analysis: Glucose; Paramax vs. Hitachi 705; Hitachi, mg/dl; Paramax, mg/dl.

Press Return at the end of each line, and then assign legends and symbols for each of the data groups in the same manner. I used Data Pairs and Regression Line.

After the titles have been entered, the graph itself must be saved as a separate file for printing and kept with the worksheet for future reference. It is most convenient to use the same name for both. For this example, I used GLUCOSE for both the graph and file names.

All of the data entry is now complete, and the graph, similar to Figure III, will appear on screen. Pressing any key will remove it, so this is a good point at which to save the worksheet for future use. The section of the worksheet containing your data and the graph should be printed using the Lotus Print Graph program.

Days of analysis are now matters of minutes. The program offers a cost savings for the time saved, but also a comfort savings when you realize you don't have to take it home to finish.

Printer friendly Cite/link Email Feedback | |

Author: | Ulstein, Howard |
---|---|

Publication: | Medical Laboratory Observer |

Article Type: | column |

Date: | Aug 1, 1989 |

Words: | 1388 |

Previous Article: | A quality assurance manual for the office laboratory. |

Next Article: | House panel approves $95 million cut in lab payments for 1990. |

Topics: |