Microsoft Office Tutorials and References

In Depth Information

**Syntax**

77. Use TRUE for the next argument, which asks whether the intercept

should be forced to be 0. This is not a requirement in the current

situation. You want to allow the intercept to be calculated nor-

mally.

88. Use 1 or TRUE for the statsargument.

99. Although you have now typed the complete formula,

=LINEST(E4:E95,B4:D95,TRUE,TRUE)

=LINEST(E4:E95,B4:D95,TRUE,TRUE), do not press the Enter key.

This is one formula that returns many results. You have to tell

Excel to interpret the formula as an array formula. To do this,

hold down Ctrl+Shift while pressing Enter. The function returns

a seemingly meaningless range of numbers, as shown in
Figure

10.

10. Start labeling the regression results in the upper-right corner.

The value in the upper-right corner is the y-intercept. This is

equivalent to the result of the INTERCEPT function.

11.

11. Working in the top row from right to left, look at the slopes of the

independent variables. These appear backward from how you ori-

ginally specified them. Your independent variables were temperat-

ure, weekend, and rain. The slope for the last independent variable

is in the top-left corner of the results. In
Figure 14.16
,
cell G4 is

the slope associated with rain. Cell H4 is the slope associated

with weekend. Cell H5 is the slope associated with temperature.

12.

12. Take a look at these numbers for a second to see if they make

sense. The intercept says you are going to sell
–
75 snow cones

each day. This initially seems wrong. However, the value in

column I says that you will sell 2.6 snow cones for every degree

of temperature. Because the lowest minimum high temperature for

the summer would be about 60 degrees, the result suggests that you

would sell a minimum of (60 × 2.6), or about 156 snow cones, due to

temperature. Adding the
–
75 and 156 gets you to a minimum of 80

snow cones on a sunny day. Cell H4 suggests that you would sell

about 52 extra snow cones on a weekend. Cell G4 suggests that you

would sell 102 fewer snow cones on a rainy day.

13.

13. Fill in the rest of the labels for statistics. The second row of the

results shows the standard error for the number above it. The

first column of the third row returns the all-important r-

squared value. If this value is close to 1, your model is doing a