Polynomials in excel - Microsoft Community

so here's jist. i'm pulling data (the stuff below) , want create polynomial regression predict next number. problem is, don't want have go through charting process or data analysis because doing 500 different items. there way via 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 moderator appropriate forum category.***

[resubmitted major second thoughts....]

johnnyd wrote:

i'm pulling data (the stuff below) , want create polynomial regression predict next number. problem is, don't want have go through charting process or data analysis because doing 500 different items. there way via sort of formula?

it is always idea chart data first.  might discover polynomial trendline not fit data well.  example, perhaps order-6 polynomial indeed fit.  wit:

nevertheless, if polynomial trendline fits original data well, poor predictor of y-values outside range of original data -- unless original data related polynomial function similar form of excel trendline.

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

but to answer question:  use linest function.

first, need decide degree of polynomial use.  again, difficult unless chart data first.

suppose choose order-6 polynomial.  select 7 original cells , array-enter (press ctrl+shift+enter instead of enter) formula of form:

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

where ydata , xdata ranges contain original data (e.g. a2:r2 , a1:r1).

finally, generate estimated-y values based on coefficents returned linest, might enter formula of form:

=seriessum(a1, 6, -1, $a$4:$g$4)

where a4:g4 range linest results, , a1 might first cell in xdata range.

Office / Excel / Microsoft Office Programming / Unknown/other