coefficients of polynomial

[Doublee]

How do I calculate the coefficients of a 5th order polynomial? A reference
would suffice. The data consists of up to 50 equally spaced data points and
consists of real positive numbers. I am already aware of the equation as
displayed in Excel on the chart but the format is not useful to me because I
want to embed the calculations of the coefficients into an Excel 2007 cell.

 

[Lori]

Select a 6 column x 1 row range and enter the array formula:

=LINEST(B2:B51,A2:A51^{1,2,3,4,5})

Use ctrl+shift+enter to exeute.

e.g. with

x 1 2 3 4 5 6
y 3 5 7 8 9 11

the formula returns the six coefficients in descending order:

y = -0.016666667x^5 + 0.333333333x^4 -2.416666667x^3 +7.666666667x^2
-8.566666667x +6

 

[Joel]

I wouldn't try calculating the coeificants yourself. It is not easy and
there are many approximations on how tto do it. You probably won't get the
same answers as the numbers on the graph.

I would take the coieffficants that excel got and create a formula base on
those numbers such as

=($A$1 * (A10^5)) + ($A$2 * (A10^4)) + ($A$3 *(A10^3) + ($A$4 * (A10^2)) +
($A$5 * (A10)) + $A$6


Then you can plot any value of N (cell A10) and get a value of Y. I thought
about doing the same thing a couple of years ago, but gave up because it was
more complicated than I expected. Excel already gave me numbers that was
accurate. There are lots of approximations on geting the Best fit. I made
some minor adjustments to the standard methods in excel. I needed a Y
Intercept that was attone so I changed the settting in my graph.

 

[Doublee]

What I meant to say is that I need the equations for the coefficients of the
polynomial to put into a Fortran program so that I can use them in a do loop
in a program that I am writing. I understand that the the math might be
complicated but I am willing to try it. A published reference that contains
this information would suffice.

 

[Jerry W. Lewis]

http://lib.stat.cmu.edu/apstat/274
or
http://lib.stat.cmu.edu/apstat/274-90

give Fortran (or Fortran90) code for solving models that are linear in the
unkown parameters (such as polynomials).

Jerry

 

[Jerry W. Lewis]

From your new post to the old thread "Trend Line constants as Excel Cell
Values" I wonder if you were looking for a "quintic equation" similar to the
well known quadratic equation. Such a general solution for the roots of a
quintic does not exit
http://en.wikipedia.org/wiki/Quintic

Jerry