Generate Polynomial (Order 6) Formula or Results

[GreenWizard]

Is there a way to automate or fill down the 'SOLVE' column below that uses a Polynomial (Order 6) formula?

I know you can manually select the first two columns (X & Y AXIS) and create a trend line, then select Polynomial Order 6, and then click on Display Equation on Chart.

However, that process if very cumbersome, and I was wondering if there is a better solution, then having to manually generate the complex formula and then input the values below.

X_AXIS	Y_AXIS	SOLVE
30.30947​	0.81975​
27.98683​	0.73169​
26.69383​	0.68889​
25.25597​	0.61317​
24.61728​	0.6​
23.51276​	0.54239​
22.79177​	0.51193​
21.75638​	0.45103​
20.72675​	0.38683​
20.90864​	0.38683​
19.94733​	0.35391​
19.13745​	0.30123​
18.72346​	0.27901​
18.81728​	0.28807​
18.00494​	0.24691​
18.1144​	0.26831​
17.24691​	0.20823​
16.77695​	0.19424​
16.77366​	0.20658​
16.56379​	0.1786​
15.81811​	0.15885​
15.79177​	0.15144​
15.41975​	0.13169​
15.3572​	0.14403​
14.79012​	0.1284​
14.62716​	0.13004​
14.107​	0.09547​
13.86831​	0.10123​
13.72428​	0.08971​
13.4321​	0.09547​
13.49547​	0.10123​
13.19012​	0.08148​
13.23457​	0.07325​
13.00082​	0.07654​
12.53416​	0.05185​
12.33169​	0.0749​
12.10947​	0.05514​
11.88395​	0.04115​
11.82469​	0.05597​
11.58683​	0.04938​
11.61317​	0.04362​
11.3465​	0.04609​
11.17942​	0.0321​
11.01317​	0.02963​
11.05926​	0.04033​
10.58354​	0.0321​
10.56872​	0.0214​
10.75309​	0.03539​
10.08971​	0.02634​
9.880658​	0.01893​
9.813992​	0.02058​
9.61893​	0.02222​
9.531687​	0.02058​
9.169547​	0.01152​
9.295473​	0.01646​
8.898765​	0.01399​
9.181893​	0.01893​
9.111934​	0.01646​
8.86749​	0.01811​
8.488066​	0.00905​
8.415638​	0.01152​
8.488889​	0.01646​
8.150617​	0.01152​
8.032922​	0.01399​
7.979424​	0.00741​
7.756379​	0.00988​
7.800823​	0.0107​
7.696296​	0.00905​
7.474074​	0.00741​
7.211523​	0.00905​
7.366255​	0.00905​
7.016461​	0.00576​
6.920165​	0.00576​
6.975309​	0.00494​
6.738272​	0.00741​
6.455967​	0.00165​
6.441975​	0.00576​
6.78107​	0.00658​
6.279012​	0.00329​
6.171193​	0.00165​
6.184362​	0.00412​
5.927572​	0.00247​
5.790947​	0.00494​
5.662551​	0.00329​
5.493827​	0.00082​
5.434568​	0.00329​
5.014815​	0.00412​
5.1893​	0.00247​
4.937449​	0.00165​
4.861728​	0.00247​
4.934156​	0.00329​
4.669136​	0.00082​
4.671605​	0.00082​
4.373663​	0.00247​
4.197531​	0.00165​
4.038683​	0.00082​
3.750617​	0.00247​
3.604115​	0.00247​
3.358848​	0​
2.801843​	0​
0​	0​

 

[Anthony47]

You can get the parametres for the curve using the function LINEST
For example, with your data:
In G2 insert the formula

=LINEST(B2:B100,(A2:A100)^{1,2,3,4,5,6},,1)

If your Excel version doesn't support Dynamic Arrays then you have to input it in array format:
-select G2:M6
-set the formula in the formula bar
-confirm the formula using the combination Contr-Shift-Enter

The first line of the outputs are the coefficients for X^6 to X^0
The data in G4 is the "R-square", and index that states how reliable is the calculated trend (1 means "total"; 0 means "nothing")

I used these coefficients to check how good are the coefficients:
In D2 I calculated the expected Y for the first X using the formula

=A2^6*$G$2+A2^5*$H$2+A2^4*$I$2+A2^3*$J$2+A2^2*$K$2+A2*$L$2+$M$2

In E2 I calculated the error using the formula

=D2-B2

Then copy down

The errors stay between -0.016 and 0.013; quite good, as the R-square=0.999 anticipates

Please give a feedback

Was not able to insert XL2BB output (column width was shrinked, thus row height very high), an image will suffice