USING EXCEL TO FIND MEASURES OF ASSOCIATION by Edward F. Stafford, Jr., PhD
Professor of Management Science, UAH

Purpose of Handout
The purpose of this handout is to describe how to obtain measures of association between two variables using the Microsoft Excel software. These measures include covariance and correlation. For the most part, each measure may be obtained in two ways: (1) “by hand”; and (2) by Excel’s fX function operator. “By hand” does not mean to actually do the computations by hand; rather, it means to use Excel for statistical computations as though the fX function operator did not exist.

Example Problem
An example problem is used to demonstrate all of the features described in this handout. The problem is extracted from Case Problem 2, “National Health Care Association,” Chapter 3 of the Anderson, Sweeney, and Williams textbook assigned for this course. In particular, the “University Hospitals” data is used in this handout. The data are scales indicating respondent’s “degree of satisfaction” in their work, their pay, and their opportunities for promotion. Scale values range from 0 to 100. The actual data values used are shown in Figure 1. The user may acquire these data, user-ready, in an Excel file by going to Dr. Stafford’s home page on the web {http://cas.uah.edu/stafford/}, then clicking on the following, in order: (1) MSC 287; (2) scroll down then Special Handouts; (3) Excel Materials including Instructions for Statistical Calculations {click on “GOTO Link”}; (4) And finally on Template from “Using Excel to Find Measures of Association.” Save the resultant Excel file to a floppy disk, then use it to follow the remainder of this handout. Note: the template has all the work tables necessary to work through this handout example problem.

Figure 1. Example Problem Data on Solution Template

MSC 287 Handout

USING EXCEL TO FIND MEASURES OF ASSOCIATION

Dr. Stafford

06/08/01

Page 1

There are three variables in the sample data. We will compute each measure of variability six times (3 variables)x(2 ways of computing). The measures will be posted to a Solutions Table as shown in Figure 2. This template is included in the Excel spreadsheet described above.

Figure 2. Template for Results of Calculations, Measures of Association Sorting the Data
DO NOT SORT THIS DATA. Sorting data before computing measures of association causes X-Y pairs of observations to be separated, thus invalidating the values obtained.

Preliminary Activities
There is space below the original data for computing the Sum (Totals), Count, Mean, and Standard Deviation (Std Dev) for each of the three variables. Previous handouts and assignments have explained how to do this. Use the 3 icon and the fX icon to accomplish these computations. Dragging can speed up this process. The results are shown in Figure 3.

Figure 3. Summary Statistics on Original Data
Next, copy the original data into the proper columns of “Work Space for Hand Computations” table on the spreadsheet. We will be using these data to compute various measures necessary to compute the covariance and correlation values. Now that we know how to clean up unwanted lines {see “Using Excel to Find Measures of Variability”}, clean up the unwanted lines just as soon as they appear on your spread sheet. Also, remember to save often.

Preparing to Compute Association Measures by Hand
The computational formula for the sample covariance, sXY, between two variables X and Y, is as follows:

s XY

 n  n   ∑ X i   ∑Yi    n  i =1   i =1   ∑ X iYi −  n = i =1 n−1
Dr. Stafford 06/08/01 Page 2

MSC 287 Handout

USING EXCEL TO FIND MEASURES OF ASSOCIATION

And the computational formula for the sample correlation, rXY, between two variables X and Y, takes on one of two forms as shown in the following:

rXY =

s XY = (s X )( sY )

 n   n  ∑ X i   ∑Yi  n    ∑ X iYi − i =1 n i =1 i =1   n  ∑X i  n   ∑ X i2 − i =1 n i =1
2

 n   ∑Yi  n   2 ∑Yi − i =1n i =1

2

where sX and sY are the standard deviations of the X- and Y-variables. Thus we need to compute 3X, 3Y, 3X2, 3Y2, and 3XY for each pair of variables for which we want to measure covariance and correlation. The “Work Space for Hand Computations” is now set up to accomplish the computation of all required summation values. Start by placing the cursor in cell I10, the first value for Work2, then place the cursor in the equation box and type “=H10^2". Press Enter and the value 7056 {84 x 84} appears in this cell. Drag cell I10 down the length of the column, but not beyond the table end line. Repeat this process for cell K10 {type “=J10^2" in the equation box} and cell M10 {type “=L10^2" in the equation box} , then drag these columns to complete the computations of the squares of the three variables. Now would be a good time to clean up your unwanted lines. Next, we wish to compute the cross-products of each pair of variables. Place your cursor in cell N10 and type “=H10*J10" in the equation box to compute the first value for (Work)x(Pay). Repeat for cell O10 {type ”=H10*L10" in the equation box} for the (Work)x(Promote) cross-product, and for cell P10 {type “=J10*L10" in the equation box} for the (Pay)x(Promote) cross-product. Then, drag these three columns down to the table end line, clean up unwanted lines. Place your cursor in cell H24, click on the 3 icon, and seeing that the flashing lines surround the complete set of Work values, press Enter. The 3X for the Work variable appears in this cell. Drag cell H24 to the right under the whole table. This will compute all summations for you. When done, your results should look like Figure 4.

Figure 4. Results of Work on Summations for Hand Calculations.

MSC 287 Handout

USING EXCEL TO FIND MEASURES OF ASSOCIATION

Dr. Stafford

06/08/01

Page 3

Computing the Association Measures by Hand
To compute the covariance between the Work and Pay variables, place the cursor in cell H4, then type “=(N24-(H24*J24/D20))/(D20-1)” in the equation box and press the Enter key. The value 42.68131868 appears in cell H4. This is the covariance between Work and Pay. We will reduce the number of decimal digits shown shortly. Repeat this step for cell J4 for the Work-Promote covariance {type “=(O24-(H24*L24/D20))/(D20-1)” in the equation box}; and for cell L4 for the Pay-Promote covariance {type “=(P24-(J24*L24/D20))/(D20-1)” in the equation box}. These actions result in a value of -17.05494505 for the Work-Promote covariance and a value of 8.868131868 for the Pay-Promote covariance. To compute the correlation between the Work and Pay variables, place the cursor in cell H6 and type “=H4/(D22*E22)” in the equation box. Upon pressing Enter, the value xx appears in this cell. This is the correlation between the Work and Pay variables. The equation used here is the first form of the correlation equation: sXY/[(sX))( sY)]. You may use the other form, but it is considerably longer, and it opens up many more opportunities to make mistakes. Repeat the steps in the above paragraph for cell J6 for the Work-Promote correlation {type “=J4/(D22*F22)” in the equation box}; and for cell L6 for the Pay-Promote correlation {type “=L4/(E22*F22)” in the equation box. Finally, curse over all six of these values, click on Format, then Cells, then choose the Number card and highlight Number, then set decimals to 3. This cleans up our results by hand, and your table should now look like Figure 5.

Figure 5. Results after Finishing the “by Hand” Computations. Computing the Association Measures using Excel
The Excel fX icon function for computing the covariance between two variables is COVAR. Place the cursor in cell P4, click on the fX icon, select Statistical, then COVAR. The Past Function box should now look like Figure 6. Click OK to get the data dialog box. The two inputs to the COVAR data dialog box are the arrays of the variables for which the Covariance is desired. With the cursor in Array 1, curse over the values for the Work variable. Then with the cursor in Array 2, curse over the values for the Pay variable. Your COVAR data dialog box should now look like Figure 7. Press Enter and the value 39.63265306 appears in cell P4. Note that this value does not correspond to the “by-Hand” value calculated above. We will examine why this is so shortly. Repeat the above procedure for cell R4 for the Work-Promote Covariance {value computed = 15.83673469}; and for cell T4 for the Pay-Promote Covariance {value computed = 8.234693878}. Again, these values do not match those computed “by Hand”. The Excel fX icon function for computing the correlation between two variables is CORREL. Place the cursor in cell P6, click on the fX icon, select Statistical, then CORREL. Click OK to get the data dialog box for CORREL. The arguments are the same here as for the COVAR data dialog box. Enter the Work variable addresses in Array1 and the Pay addresses in Array 2, and press OK. The value 0.37409266 appears in cell P6. This is the same value we got “by Hand”.

MSC 287 Handout

USING EXCEL TO FIND MEASURES OF ASSOCIATION

Dr. Stafford

06/08/01

Page 4

Figure 6. Past Function Box for Finding Covariance.

Figure 7. COVAR Data Dialog Box Ready to Compute Work-Pay Covariance.
Repeat the above procedure for cell R6 for the Work-Promote Correlation {value computed = 0.120762883}; and for cell T6 for the Pay-Promote Correlation {value computed = 0.029079017}. These value also match those computed “by Hand”. Clean up these latest values by setting the decimals showing to 3. The resultant Solutions Table should now look like that in Figure 8.

MSC 287 Handout

USING EXCEL TO FIND MEASURES OF ASSOCIATION

Dr. Stafford

06/08/01

Page 5

Figure 8. Finished Results Table. Why Excel’s Covariance Values Do Not Match by-Hand Calculations
The reason that Excel’s values for Covariance do not match those of by-Hand calculations is that Excel assumes that, for Covariance, we are using a population instead of a sample. That is, by Hand, we divided by (n-1) while Excel divided by n. To prove this assertion, multiply Excel’s values by n/(n-1) {14/13 in the example problem case}. This is done in an add-on table on the spreadsheet, with the results shown in Figure 9.

Figure 9. Computations Showing Excel Computes Covariance As Though for a Population.

MSC 287 Handout

USING EXCEL TO FIND MEASURES OF ASSOCIATION

Dr. Stafford

06/08/01

Page 6