Microsoft Office Tutorials and References

In Depth Information

(f) save the ﬁle as: CRAB3

Answer the following questions using your Excel printout:

1. What is the correlation?

2. What is the y-intercept?

3. What is the slope of the line?

4. What is the regression equation for these data (use two decimal places for

the y-intercept and the slope)?

5. Use that regression equation to predict the gill weight you would expect

for a body weight of 15 grams.

(Note that this correlation is not the multiple correlation as the Excel table

indicates, but is merely the correlation r instead.)

Note that you found a positive correlation of

.87 between body weight

and gill weight in crabs above. You know that the correlation is a positive

correlation for two reasons: (1) the regression line slopes upward and to

the right on the chart, signaling a positive correlation, and (2) the slope is

þ

þ

14.58 which also tells you that the correlation is a positive correlation.

But how does Excel treat
negative correlations
?

Important note:

Since Excel does not recognize negative correlations in the SUMMARY

OUTPUT but treats all correlations as if they were positive correlations,

you need to be careful to note when there is a negative correlation

between the two variables under study.

You know that the correlation is negative when:

(1)
The slope, b, is a negative number which can only
occur when there is

a negative correlation.

(2) The chart clearly shows a downward slope in the regression line,

which can only happen when the correlation is negative.

3. Suppose that you wanted to study mayﬂies in lakes in western Montana.

Mayﬂies are common aquatic insects found in rivers, streams, and lakes across

the United States. You are trying to study the relationship between the body

length and the forewing length of mayﬂies.

You want to study the body length (from the head to the start of the tail) in

millimeters (mm) and the forewing (front wing) length (in mm). You have

decided to use body length as the predictor and forewing length as the criterion.

You have collected a small sample of mayﬂies from various lakes in Montana to

test your Excel skills and to make sure that you can do this type of research. The

hypothetical data appear in Fig.
6.32
:

Create an Excel spreadsheet and enter the data using body length as the

independent variable (predictor) and the forewing length as the dependent variable

(criterion). Underneath the table, use Excel’s

correl
function to ﬁnd the

correlation between these two variables. Label the correlation and place it

underneath the table; then round off the correlation to two decimal places.

¼

Search JabSto ::

Custom Search