# 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?

### 2 Answers

- 1 decade agoFavorite Answer
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

and your answer is :

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.- Login to reply the answers

- ehrlichLv 43 years ago
Polynomial Trendline Excel

Source(s): https://shorte.im/a0cOG- Login to reply the answers