1853 words - 8 pages

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 ...view middle of the document...

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

Beat writer's block and start your paper with the best examples