Can you relate? Correlation and simple linear regression - Improving the User Experience through Practical Data Analytics - page 212

straight-line it . Clearly, there is a relationship, perhaps somewhat strong, between Y
and X in the bottom right plot. But, it is a “U-shaped” curve. A straight line does not
do a good job at all of itting that data. The best-itting line would be horizontal, or
very close to it, and the value of r would, correspondingly, be equal to or very close
to zero. After all, what does Y do as X increases? For the irst half of the X's, Y goes
down; for the second half of the X's, Y goes up. We might say that, on average , Y
does not do anything —hence the zero slope of the best-itting line and a value of r
of zero.
Remember, if a line has a slope of zero (and is, thus, horizontal), it means that as
X changes, Y does not change at all. After all, if a line is Y = 4 + 0*X, with its zero
slope, no matter what X you plug in, the Y stays the same—indeed, Y is totally unaf-
fected by X; that is why we noted earlier that a zero value for r indicates that there is
no linear relationship at all between the two variables.

9.3.1 EXCEL
We will now describe how to ind r using Excel. We will analyze the real-world
Behemoth.com data a bit later. Let us use our limited data set to illustrate the inding of
the correlation, r, using Excel. Suppose we have the ive data points in Table 9.2 (which
we can envision coming from respective 5-point Likert scales).
For the most part, when X is larger, Y is larger, so we would expect a positive
value for r.
First we open Data Analysis in Excel and identify “Correlation.” See the arrow
in Figure 9.2 . Then we click “OK.” This gives us the Correlation dialog box, shown
in Figure 9.3 .
We enter the (input) data range (see vertical arrow in Figure 9.3 ), and, we ask the
output to be on a page arbitrarily named “paul” (see horizontal arrow in Figure 9.3 ).
After we click “OK,” we get the answer—the correlation between the two variables—
as shown in Figure 9.4 .
Figure 9.4 tells us that the correlation, r, equals +0.895. We noted earlier that
we anticipated a positive value, and, indeed, we do get a positive value. Notice
that in Figure 9.4 , the top right cell is empty. That is because the correlation
coeficient between two variables is the same, regardless of which is the “Y” and
which is the “X.” In other words, the correlation between the Column 2 variable

Table 9.2 Illustrative Data

X Y

5 4

4 4

2 1

3 3

4 3