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

[Resubmitted with major second thoughts....]

JohnnyD wrote:

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?

It is always a good idea to chart the data first.  Then you might discover that a polynomial trendline does not really fit the data well.  But for your example, perhaps an order-6 polynomial is indeed a good fit.  To wit:

Nevertheless, even if a polynomial trendline fits your original data well, it is usually a poor predictor of y-values outside the range of the original data -- unless the original data is truly related by a polynomial function similar to the form of the Excel trendline.

You might see why if you extend your x-range by several months.

But to answer your question:  you would use the LINEST function.

First, you need to decide what degree of polynomial to use.  Again, that is difficult to do unless you chart the data first.

Suppose you choose an order-6 polynomial.  Then you would select 7 original cells and array-enter (press ctrl+shift+Enter instead of just Enter) a formula of the form:

=LINEST(ydata, xdata^{1,2,3,4,5,6}))

where ydata and xdata are the ranges that contain the original data (e.g. A2:R2 and A1:R1).

Finally, to generate estimated-y values based on the coefficents returned by LINEST, you might enter a formula of the form:

=SERIESSUM(A1, 6, -1, $A$4:$G$4)

where A4:G4 is the range with LINEST results, and A1 might be the first cell in the xdata range.

Answer 2

Errata....  I wrote:

Suppose you choose an order-6 polynomial.  Then you would select 7 original cells and array-enter (press ctrl+shift+Enter instead of just Enter) a formula of the form:

=LINEST(ydata, xdata^{1,2,3,4,5,6}))

That works when xdata is a vertical range (column).  However, when xdata is a horizontal range (row) like A1:R1, the array constant must also be a row separated by semicolon, not comma, to wit:

=LINEST(ydata, xdata^{1;2;3;4;5;6}))

---

Also, I forgot to mention another issue with using LINEST to calculate the coefficients of a polynomial, exponential and some power trendlines.

When xdata contains dates or any other large numbers, the range of values in the resulting array parameters exceed the limits of binary floating-point arithmetic.  So we lose significant precision, and both the coefficients and possibly the formula for estimated-y lose significant accuracy.

For example, 6/30/2017 is the integer 42916.  42916^6 is about 6.25E+27, which far exceeds the largest integer that Excel can represent accurate, namely less than 9.01E+15.

To minimize the loss of precision, it would be better for xdata to be relative dates starting with 1.  In your example, let xdata be the range A3:R3, where A3 is 1, B3 is =B1-$A$1, and B3 is copied into C3:R3.

Even then, the largest relative date should be 456 or less.  Note that for 6/30/2017 in R1, R3 would be 518.  That is probably close enough and an acceptable loss of precision.

Answer 3

I truly appreciate the help you've given! As an intern, this will be great brownie points. If I have any questions, I'll hit you with another reply.