Microsoft Office Tutorials and References

In Depth Information

**Troubleshooting LINEST**

good job of predicting the data. The value of 0.95 shows that this

model is fairly good. Row 3, column 2 shows the standard error of

Y. It is normal to have #N/A in any additional columns of row 3.

Row 4 contains the Fstatistic and degrees of freedom. Row 5 con-

tains the sum of squares of the regression and the residual sum of

squares. This is the number that Excel is trying to minimize when it

fits the line using least squares.

14.

14. In column L, build a formula to predict sales with the results of

the regression. This formula would be Intercept + Slope temp × Temp

+ Slope weekend × Weekend + Slope rain × Rain. The formula in cell

L4 is therefore =$J$4+$I$4*B4+$H$4*C4+$G$4*D4.

15.

15. To visually compare the data, plot the actuals in column E and

the prediction in column L on a chart. The chart in rows 12:22

shows that the prediction is tracking fairly well with the actual.

There was a cold, rainy weekday near the beginning where the model

predicted
–
10 sales versus an actual of 25.

16.

16. For another interesting test, calculate the residual or error for

each day. The data in column M is the difference between L and E.

Plot this data. You should see many small positive and negative

values. (Notice that the scale of this chart is smaller than the

original chart.) The values should swing from positive to negative

frequently. The amount of scatter should not vary over time. You

should not see many clusters of points that are either positive or

negative. The chart in rows 24:34 shows that there are many posit-

ive residuals early in the summer, and fewer later in the summer.

This might mean that the model is less successful at lower June

temperatures than at higher August temperatures. Perhaps only

real snow cone fans buy the product at temperatures of 60 to 80.

Above 80 degrees, more people might buy the product.

Troubleshooting

Troubleshooting
LINEST

LINEST

Remember that LINEST returns an array of values. In addition, you need to

select a large enough range before entering the function, and you need to use

Ctrl+Shift+Enter to enter the formula.

If you forget to use Ctrl+Shift+Enter, Excel returns just the top-left cell

from the result set. In the data set in
Figure 14.15
,
this would be the slope