Microsoft Office Tutorials and References
In Depth Information
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, Watt, and Hart ( 2007 ).
You could also use the results of the above table in the formula for computing the
correlation r in the following way:
¼
ð
X
X
Þð
Y
Y
Þ
correlation r
[ (1 / (n-1)) x
] / (STDEV x x STDEV y )
¼
correlation r
[ (1/7) x 1.09 ] / [ (.48) x (.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 find 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 find the correlation between
two variables
Suppose that an aquatic biologist in the state of Wisconsin in the U.S.A. has
asked you to find the relationship between the weight of a specific species of female
fish and the number of eggs produced by that fish at the end of its pregnancy. The
aquatic biologist is studying female Walleye fish ( Sander vitreus ) which is a
popular fish in the United States and Canada.
To test your Excel skills, you take a random sample of fish 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 .
Important note: Note that the eggs produced have been rounded off to thousands,
so that a fish that produces 65,000 eggs would be recorded as 65.
You want to determine if there is a relationship between the weight and eggs
produced, and you decide to use a correlation to determine this relationship. Let’s
call the weight scores the predictor, X, and eggs produced, the criterion, Y.
Create an Excel spreadsheet with the following information:
A3: FEMALE WALLEYE FISH IN WISCONSIN
B5: Is there a relationship between weight and the number of eggs produced?
B7: Weight in grams (g)
C7:
Eggs produced (thousands)
B8:
1245
C8:
58
Search JabSto ::




Custom Search