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
Search JabSto ::




Custom Search