Polynomial fit coefficients without a graph?

[user]

Is it possible to get the coefficients to a polynomial curve fit without
having to graph the data and use "Add Tredline." I am looking for third
order polynomials and above. In addition, I have a large data set and
would like to generate a set of coefficients for each x-y set.

Thank you,

Brian

(e-mail address removed)

 

[Jean-Claude Arbaut]

Le 14/06/2005 21:52, dans (e-mail address removed),

Is it possible to get the coefficients to a polynomial curve fit without
having to graph the data and use "Add Tredline." I am looking for third
order polynomials and above. In addition, I have a large data set and
would like to generate a set of coefficients for each x-y set.

Thank you,

Brian

(e-mail address removed)

I don't think Excel has integrated functions for that, but the
computation is fairly easy: just a multilinear regression

X = [1,x,x^2...,x^p], n x p+1 matrix
Y = [y], n x 1 matrix
C = [c_0...c_p]', p+1 x 1 matrix

Then you would like X * C = Y, over-determined, so you use:
(X'*X) * C = (X'*Y)

C = inv(X'*X) * (X'*Y)

Actually, you don't need to compute a matrix inverse, only to solve
a linear system. I don't know which is easier to compute in Excel.
If you want to use many data sets, maybe you'll need a macro.

 

[Mike Middleton]

Brian -

Go to

http://www.tushar-mehta.com/excel/tips/trendline_coefficients.htm

and scroll down to the section "Regression with polynomials."

Tushar says "To get the regression for a cubic best fit, one would use
=LINEST(y-range, x-range^{1,2,3})."

This function must be array-entered (Control+Shift+Enter). You could use
OFFSET to get individual coefficients.

Since you mention "third order polynomials and above," I recommend that you
also read the section "Over-specifying a regression."

- Mike
www.mikemiddleton.com