Microsoft Office Tutorials and References

In Depth Information

Step

7

Multiply the answer for step 2 above by the answer for step 6 (0.14286 9 1.09)

0.1557

8

Multiply the STDEV of X times the STDEV of Y (0.48 9 0.39)

0.1872

9

Finally, divide the answer from step 7 by the answer from step 8 (0.1557 divided

by 0.1872)

+0.83

This number of 0.83 is the correlation between HSGPA (X) and FRGPA (Y) for

these 8 students. The number +0.83 means that there is a strong, positive

correlation between these two variables. That is, as HSGPA increases, FRGPA

increases. For a more detailed discussion of correlation, see Zikmund and Babin

2010
) and McCleery et al. (
2007
).

You could also use the results of the above table in the formula for computing

the correlation r in the following way:

correlation r = [(1/(n - 1))
R
ð
X
X
Þð
Y
Y
Þ= ð
STDEV
x
STDEV
y
Þ

correlation r
¼½ð
1
=
7
Þ
1
:
09
=½ð
0
:
48
Þð
0
:
39
Þ

correlation
¼
r
¼
0
:
83

When you use Excel for these computations, you obtain a slightly different

correlation of +0.82 because Excel uses 16 decimal places for all numbers and

computations and is, therefore, more accurate than your calculator.

Now, let’s discuss how you can use Excel to ﬁnd the correlation between two

variables in a much simpler, and much faster, fashion than using your calculator.

6.2 Using Excel to Compute a Correlation Between Two

Variables

Objective: To use Excel to ﬁnd the correlation between two variables

Suppose that an aquatic biologist in the state of Wisconsin in the U.S.A. has

asked you to ﬁnd the relationship between the weight of a speciﬁc species of

female ﬁsh and the number of eggs produced by that ﬁsh at the end of its

pregnancy. The aquatic biologist is studying female Walleye ﬁsh (Sander vitreus)

which is a popular ﬁsh in the United States and Canada.

To test your Excel skills, you take a random sample of ﬁsh near the end of their

pregnancy and weigh them (to the nearest gram) and then count the number of

eggs they produced (in thousands). The hypothetical data appear in Fig.
6.9
.

Search JabSto ::

Custom Search