Trendline Equations - Can't Get *Future* Values from 3rd Order Polynomial

[schnide]

Hi All,

For several years now I've used the formulas from this page:

http://spreadsheetpage.com/index.php/tip/chart_trendline_formulas/

..to great success with easier equations like Linear & Power.

However I've now tried 3rd order polynomial and I've run into a distinct problem. I can get the trendline values from existing points on the timeline, but I need to also get the future ones.

I can't attached an example so here's a link to the file:

https://www.dropbox.com/s/en0yrkzjm3rvsff/Future Values - 3rd Order Polynomial Trend.xlsx?dl=0

The Excel-produced trendline in dotted grey can clearly forecast these values, and I'd like to be able to on the yellow line, too! It's J28 and J29 I'm trying to fill.

Changing the date/actuals ranges does not work in the same way as it would for the simpler trendlines and only seems to produce errors.

Either way, all help appreciated as always.

 

[PCRIDE]

Not real sure, maybe look at some other forecast formulas.

Also at times like this when a chart doesn't do what you want it to, you need to add another series, and do whatever formula then its more of a visual effect on the chart. Layering charts essentially.

 

[schnide]

Thank you for the reply, although, have you used these formulas much before? They're usually great to extract values from a trendline from but for this particular type, it doesn't seem to project forward using the x range like the others do.

I'm sure there's an obvious reason to mathematicians why this is, but to someone who's only used to using the formulas based on instructions, I am in the dark as to how to project forward for 3rd order polynomial.

Very much hoping someone can help here.

 

[joeu2004]

I'm struggling to understand what your concern is.

First, with your formulas in column K, I do not get any errors if I copy J27:J28 and J29 and copy C27 into C28:C29. And the resulting yellow line closely tracks the chart trendline. (I am using Excel 2010.)

However, you are using a Line chart instead of an XY chart, and the trendline of a Line chart is known to be incorrect unless the x-axis values are 1, 2, 3, etc. See https://support.microsoft.com/en-us/help/211967/chart-trendline-formula-is-inaccurate-in-excel .

The Line chart trendline uses 1, 2, 3 etc for the x-axis, which are equidistant apart, not your data. Your LINEST formulas use the actual dates in column A; they are the numeric values 41395, 41426, 41456 etc, which are not equidistant apart.

(To make matters worse, the "dates" in C28:C29 include times of day. They should not.)

If you substitute 1, 2, 3 etc in column A, the values calculated in column J (and C) fit the chart trendline more closely, if not exactly.

Nevertheless, the difference is relatively small in this case.

-----

In general, it is unwise to extrapolate an polynomial trendline (or LINEST equivalent), unless the actual data is "polynomial" by its nature (rarely the case). Polynomial trendlines should be used only to interpolate between the endpoints of the original data.

This is demonstrated by the extreme drop-off of your polynomial trendline. The fact that it seems to follow the data for Mar'15 is coincidence, if not artificial. ( I cannot imagine any reason why the actual data for Mar'15 would experience a drop of more than 83%.)

In any case, that is why the predicted (trendline) value for May'15 is negative, if that is your concern.

(In fact, the chart trendline also appears to go negative for May'15.)

-----

PS.... Your use of LINEST can be simplified. Select F5:I5 and array-enter (press ctrl+shift+Enter instead of just Enter) the following formula:

=LINEST(B5:B27,A5:A27^{1,2,3})

Moreover, the formulas in column J can be of the form:

=SERIESSUM(A5,3,-1,$J$5:$I$5)

 

[joeu2004]

Corrections....

I do not get any errors if I copy J27:J28 and J29 and copy C27 into C28:C29.

That should be: copy J27 into J28:J29.

To make matters worse, the "dates" in C28:C29 include times of day. They should not.

That should be: A28:A29.

 

[schnide]

I have to say I am absolutely mystified by this.

I spent hours trying to get this working and failed, only turning to this forum eventually for help.

And yet, as you say, it seems to work fine just by copying down the equation. I apologise for wasting your time and for everyone else's who read this!

(I should state that I actually posted example data from the net, just using the same equation, as in reality it's company data that I'm using here which I couldn't post. My own version doesn't have the issues with using dates etc.)

My apologies again - I'd be happy for a mod to close or delete this thread. Thanks again for your time.