Microsoft Office Tutorials and References
In Depth Information
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-
88. Use 1 or TRUE for the statsargument.
99. Although you have now typed the complete formula,
=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. 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. 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
the slope associated with rain. Cell H4 is the slope associated
with weekend. Cell H5 is the slope associated with temperature.
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. 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