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 ﬁ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
.

Important note:
Note that the eggs produced have been rounded off to thousands,

so that a ﬁsh 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