Data Analysis with Microsoft Excel: Updated for Office 2007

336 Statistical Methods

The most often used measure of correlation is the Pearson Correlation

coeffi cient, which is usually identifi ed with the letter r. The formula for r is

r 5

a

n

i 51

(^1) xi 2 x (^21) yi 2 y 2
Åa
n
i 51
(^1) xi 2 x 22
Åa
n
i 51
(^1) yi 2 y 22
For example, the correlation of the data in Table 8-1 is
r 5
(^112) 1.8 (^2132) 3.4 (^21122) 1.8 (^2142) 3.4 21 c 1122 1.8 (^2152) 3.4 2
" 112 1.8 221 c 1122 1.8 223 " 132 3.4 221 c 1152 3.4 22
5

1.4

"2. 83 "1. 2

50 .7 63

which indicates a high positive correlation.

Correlation and Slope

Notice that the numerator in the equation for r is exactly the same as the

numerator for the slope b in the regression equation shown earlier. This is

important because it means that the slope 5 0 when r 50 and that the sign

of the slope is the same as the sign of the correlation. The slope can be any

real number, but the correlation must always be between 21 and 1 1. A cor-

relation of 1 1 means that all of the data points fall perfectly on a line of

positive slope. In such a case, all of the residuals would be 0 and the line

would pass right through the points; it would have a perfect fi t.

In terms of hypothesis testing, the following statements are equivalent:

H 0 : There is no linear relationship between the predictor variable and the

dependent variable.

H 0 : There is no population correlation between the two variables.

In other words, the correlation is zero if the slope is zero, and vice versa.

When you do a statistical test for correlation, the assumptions are the same

as the assumptions for linear regression.

Correlation and Causality

Correlation indicates the relationship between two variables without as-

suming that a change in one causes a change in the other. For example, if

you learn of a correlation between the number of extracurricular activities

and grade-point average (GPA) for high school students, does this imply that

if you raise a student’s GPA, he or she will participate in more after-school

activities? Or that if you ask the student to get more involved in extracurricular