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 ?
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 ::