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

I'm having an issue. I'm using formula =LINEST(G6:X6,G3:X3^{1;2;3;4;5;6}). Where G6:X6 is the data above (y's) and G3:X3 is the relative dates going up to 518. The LINEST formula returns "1.12748E-14" and then I use =SERIESSUM(G3,6,-1,G6:Y6). Where G3 is the start of the relative Dates '1' and G6:Y6 is the y-values including the LINEST value.

LINEST was entered using CNTRL+SHFT+ENTER and SERIESSUM was not.

Data starts in Column G (sorry for that inconvenience)

1	31	62	91	123	153	182	215	245	276	306	336	368	396	427	455	488	518
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	Array Thing	Seriessum
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	1.12748E-14	76.945

Answer 2

JohnnyD wrote:

I'm using formula =LINEST(G6:X6,G3:X3^{1;2;3;4;5;6}). Where G6:X6 is the data above (y's) and G3:X3 is the relative dates going up to 518. The LINEST formula returns "1.12748E-14" and then I use =SERIESSUM(G3,6,-1,G6:Y6).

You missed or misunderstood some details in my previous instruction.  See the explanation after the image below.

Note that columns N:U are hidden so that the image is more readable.

To array-enter the LINEST formula, select 7 horizontal cells; for example, G8:M8 shown above.  The fact that I choose (some of) the same columns as the data is just a coincidence.  LINEST returns all 7 coefficients of the polynomial in those cells.

You array-entered the LINEST formula into a single cell.  Thus, LINEST returns only the first coefficient of the polynomial, namely the coefficient for the x^6 term.

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.

In your SERIESSUM formula, the last parameter referred to the ydata (row 6), for the most part. 

You can download the Excel file "johnnyD v2.xls" (click here) [1].  Ignore any preview errors.

---

[1] https://app.box.com/s/gse7efvr9z0apwno7p4jsrxz9iqfy169

Answer 3

I deleted this earlier, but as a side note, I wonder how important your date values are?

Your dates are nothing more then the last workday of each month.  A month that ends on a Friday, Saturday, or Sunday, will all point to the same ending Friday.  Your x-values are discontinuous in other words.

To me, your x-value units are really nothing more than the integer values 1,2,3,...

With a polynomial equation, and a value, if you work back, it may point to a Saturday, which would not be a value input.

If it returned a value close to 19, then we could say it represents the last workday of the 19th month (July 17)

Again, just a thought.

Answer 4

The Dates are only important for importing the data. It can be there for visual, and will be changing every month. But the same amount of months will never change. So if I just put 1-18 above it, I can use that for calculations. Is that what you're saying?

Sorry about the double post earlier too.

Answer 5

That would be my recommendation since your units seems to be "months" and not really a specific day since we are only working with workdays.   Plus, it addresses the excellent issues mentioned by Joeu2004.