Anonymous

# Excel polynomial trendlines?

On Microsoft Excel, I need to find the unknown data points on a column chart with a polynomial trendline. The data I have is the following: 2005 = 133, 2015 = 73, 2030 = 42. And I need to find the numbers for the years 2006, 2007 etc. Can anyone help?

Relevance

I know this way:

Prepare a worksheet as below:

A B C

1 2005 133

2 2015 73

3 2030 42

4 2007

5 2006

Using “Linest” and “Index” function you can find a polynomial equation:

y = ax^3 + bx^2 + cx + d

In cell E1 type this formula = Index ( Linest (B1:B3,A1:A3^{1,2,3}),1,1) and press ctrl+shift+enter (this is an array formula)

Same as above in F1 cell type = Index ( Linest (B1:B3,A1:A3^{1,2,3}),1,2)

Same as above in G1 cell type = Index ( Linest (B1:B3,A1:A3^{1,2,3}),1,3)

Same as above in H1 cell type = Index ( Linest (B1:B3,A1:A3^{1,2,3}),1,4)

As you can see the coefficients of polynomial equations are:

a : Cell E1

b : Cell E1

c : Cell E1

d : Cell E1

a= 0.0000523315308374324

b= -0.159272428233135

c=0

d= 218612.148487136

Your equation is ready. So with a simple formula on column “C” you can evaluate the number for each year as below:

Cell c1: =(\$E\$1*A1^3)+(\$F\$1*A1^2)+(\$G\$1*A1)+\$H\$1

Cell c2: =(\$E\$1*A2^3)+(\$F\$1*A2^2)+(\$G\$1*A2)+\$H\$1

Cell c3: =(\$E\$1*A3^3)+(\$F\$1*A3^2)+(\$G\$1*A3)+\$H\$1

Cell c4: =(\$E\$1*A4^3)+(\$F\$1*A4^2)+(\$G\$1*A4)+\$H\$1

Cell c5: =(\$E\$1*A5^3)+(\$F\$1*A5^2)+(\$G\$1*A5)+\$H\$1

Finally as you see the answer for year 2007 is 118.501924669952 and for year 2006 is 125.595303610549.

Good Luck!

Source(s): I am an excel adict.