Microsoft Office Tutorials and References

In Depth Information

Now, make the columns in the Regression Summary Output section of your

spreadsheet
wider
so that you can read all of the column headings clearly.

Now, change the data in the following two cells to Number format (2 decimal

places):

B53

B66

Next, change this cell to four decimal places: B67

Now, change the format for all other numbers that are in decimal format to

number format, three decimal places, and center all numbers within their cells.

Save the resulting ﬁle as: eggs36

Print the ﬁle so that it ﬁts onto one page. (
Hint: Change the scale under “Page

Layout” to 60% to make it ﬁt.)
Your ﬁle should be like the ﬁle in Fig.
6.27
.

Note the following problem with the summary output.

Whoever wrote the computer program for this version of Excel made a mistake

and gave the name: “Multiple R” to cell A53.

This is not correct. Instead, cell A53 should say: “correlation r” since this is the

notation that we are using for the correlation between X and Y.

You can now use your printout of the regression analysis to ﬁnd the regression

equation that is the best-ﬁtting straight line through the data points.

But ﬁrst, let’s review some basic terms.

6.5.2.1 Finding the y-intercept, a, of the Regression Line

The point on the y-axis that the regression line would intersect the y-axis if it were

extended to reach the y-axis is called the “y-intercept” and
we will use the letter “a”

to stand for the y-intercept of the regression line
. The y-intercept on the

SUMMARY OUTPUT on the following page is
24.73 and appears in cell B66
.This

means that if you were to draw an imaginary line continuing down the regression

line toward the y-axis that this imaginary line would cross the y-axis at 24.73. This

is why it is called the “y-intercept.”

6.5.2.2 Finding the Slope, b, of the Regression Line

The “tilt” of the regression line is called the “slope” of the regression line. It

summarizes to what degree the regression line is either above or below a horizontal

line through the data points. If the correlation between X and Y were zero, the

regression line would be exactly horizontal to the X-axis and would have a zero

slope.

If the correlation between X and Y is positive, the regression line would “slope

upward to the right” above the X-axis
. Since the regression line in Figure
6.27

slopes upward to the right, the slope of the regression line is

þ

0.0165 as given in

Search JabSto ::

Custom Search