Microsoft Office Tutorials and References
In Depth Information
Multiply the answer for step 2 above by the answer for step 6 (0.14286 9 1.09)
Multiply the STDEV of X times the STDEV of Y (0.48 9 0.39)
Finally, divide the answer from step 7 by the answer from step 8 (0.1557 divided
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
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 ::