Polynomials in excel

Question

So here's the jist. I'm pulling data (the stuff below) and I want to create a polynomial regression that will predict the next number. The problem is, I don't want to have to go through the charting process or the data analysis because I will be doing this for roughly 500 different items. Is there a way to do it via some sort of formula?

1/29/2016	2/29/2016	3/31/2016	4/29/2016	5/31/2016	6/30/2016	7/29/2016	8/31/2016	9/30/2016	10/31/2016	11/30/2016	12/30/2016	1/31/2017	2/28/2017	3/31/2017	4/28/2017	5/31/2017	6/30/2017
3.138	3.186	3.185	3.609	3.596	3.594	3.948	3.955	3.967	3.979	4.113	4.113	5.228	5.406	5.428	5.476	5.547	5.477

***Post moved by the moderator to the appropriate forum category.***

Answer 1

Then the SERIESUM formula in G10 is =SERIESSUM(G3,6,-1,$G$8:$M$8), where G8:M8 refers to the LINEST results.  In my example, G10 is copied into H10:X10 to calculate and compare estimated-y with the original ydata.

So if I make a period "19" cell, and run the SERIESSUM into that, will that number be my prediction for period 19?

Answer 2

Joeu2004's solution seems to work for me:

Answer 3

JohnnyD wrote:

So if I just put 1-18 above it, I can use that for calculations. Is that what you're saying?

I agree with Dana on this point:  just use relative month numbers instead of dates or even relative dates.

It might be noted that although it changes the polynomial coefficients substantially, it does not change the estimated-y substantially.

JohnnyD wrote:

[joeu2004 wrote:]

Then the SERIESUM formula in G10 is =SERIESSUM(G3,6,-1,$G$8:$M$8), where G8:M8 refers to the LINEST results.  In my example, G10 is copied into H10:X10 to calculate and compare estimated-y with the original ydata.

So if I make a period "19" cell, and run the SERIESSUM into that, will that number be my prediction for period 19?

Yes.  See the image below.  Also, you can download the Excel file "johnnyD v2.xls" again (same URL).  See the "month#" worksheet.

Note that I put the trendline extrapolation (months 19 through 22) off to the side (F14:G18) to emphasize that we can calculate the SERIESSUM anywhere, not just parallel with the original data.

Also, the change to relative month number makes it easier to demonstrate the fallacy of extrapolating polynomial trendlines.  Notice how the trendline extrapolation (in red) "blows up" in just 4 months.

Note: Columns O:X are hidden so that the image is more readable.

Answer 4

Thank you for all the help! For what we're using this data for, we are looking for quick acceleration. I showed my boss and we are probably going to take the first estimated data point compared to the Seriessum that isn't an estimate.

I appreciate the help PLUS I told him that I couldn't figure it out without your help.....you still made me look good so thanks.

Answer 5

JohnnyD wrote:

Thank you for all the help! [....] we are probably going to take the first estimated data point compared to the Seriessum that isn't an estimate.

You're welcome.  And thanks to Dana for the simplification (month numbers).

But I'm not sure what you mean by "Seriessum that isn't an estimate".

I suspect you mean "isn't a good estimate", referring to the part of the trendline extrapolation that "blows up".

But to be clear:  everything that is calculated using the LINEST coefficients -- every SERIESSUM result -- is an estimated (predicted) y-value.