problem with FORECAST function

Might need to see an example of what you mean.

In cases where you are assuming the same linear trend (y=mx+b) with the same input data, I would expect the chart trendline and the FORECAST() function to yield the same results. If they are yielding different results, that suggests a difference in the input data or that the chart is assuming a different trendline type (polynomial, exponential, or other).

Originally Posted by edstevens

Excel 2007 on Win 7 Pro
In brief, structure of worksheet is this:
Col B - date (one row per day, every day)
Col S - A number, generally trending toward zero
Col S - function to predict when S will reach zero: =FORECAST(0,B$7:B10,S$7:S10)
[....]
First, very rarely there will be an aberration in the value of col S, which pulls the 'date to zero' very far forward - even into the past. I can manually delete these skewed values, but it seems there could be a better way to deal with this than deleting historical data.

First, if you want the zero intercept, it is sufficient to use the INTERCEPT function.

Second, both INTERCEPT and FORECAST return the parameters of the best-fit linear regression line, which may or may not fit the endpoints of the actual data (B7 and B10) exactly. That is the reason for the "aberration".

I'm in a rush to leave, and off-hand, I cannot think of a way to avoid the problem automatically, other than using just B7 and B10 with S7 and S10 to define the line. Of course, even that could result in an exaggerated date for the zero intercept.

Originally Posted by edstevens

Second, I also have a chart with a trendline to visualize 'date to zero'. This trendline is show a zero date much further into the future than the formula in the spreadsheet. [....] My reading said the charting trendline uses the FORECAST function, but since I have such a discrepancy it would appear I've got apples and oranges, but don't know where to look or how to reconcile the difference.

Only a Microsoft implementor can tell us how chart trendlines are implemented. Anyone else is just speculating. It is "likely" that chart trendline uses the same regression that FORECAST uses. But we cannot know for sure.

For example, it is not unusual to see discrepancies between LINEST and chart trendlines. So clearly, sometimes chart trendlines use a different method of regression, or they use different data.

Moreover, one reason for "differences" is failing to see and use the full 15 significant digits of the coefficients in the trendline equation.

That said, the devil is in the details. It would be helpful if you provided a concrete example -- an example Excel file with the original data.

I have not been through it thoroughly. A few observations:

1) LINEST() function using column C for known_y's and column B for known_x's returns the same parameters as the chart trendline. Solve this y=mx+b for x when y=0 yields approximately the same result as the chart trendline.
2) LINEST() function using column B for known_y's and column C for known_x's returns same intercept as your FORECAST() function. Theoretically, x-intercept should be b/m from (1) regression and slope (dx/dy) should be 1/m from (1) regression.

I suspect that something in those "outliers" is creating some instabilities for the regression algorithms. I'm not sure exactly how I would approach this problem, but I would suggest that you will need to consider a more careful approach to your thought process to improve the numerical stability of the regressions.