Polynomial curve Fit - but not on graph

[RGK]

I am doing a 2nd order polynomial curve fit to my data on an xy chart - that works fine, and I select the "display equation" option to see the equation fit results. However, I need to use the coefficients to do further calculations. I cannot seem to find anything in the online help that tells me how to get the coefficients into a cell for other processing. I'd expect a function that maybe does something like:

=poly2curveA(B7:C29) which would give me the x-squared coefficient and
=poly2curveB(B7:C29) which would giive the x-coeff.

Does such a thing not exist as the help seems to suggest? It seems crazy to think that there would be the routine to calculate the fits in a graphical context, but not in the ordinary cell function context.

Anyway - any help is appreciated.

Ross.

 

[Jerry W. Lewis]

Assuming that B7:B29 is the y data and C7:C29 is the x data select three
adjacent cells in the same row and use
=LINEST(B7:B29,C7:C29^{1,2})
array entered (Ctrl-Shift-Enter).

Alternately, David Braden has posted VBA code to extract the
coefficients directly from the chart into cells

http://groups.google.com/[email protected]

The advantage of using the chart coefficients (either manually or via
Braden's code) is that LINEST in Excel versions prior to 2003 uses a
numerically poor algorithm that can give inaccurate results with some
data sets. The chart trendline (extracted by Braden's code) is much
better numerically

Jerry

 

[RGK]

Thank you for that help Jerry!

I am embarassed to say that I discovered later that I had asked the very same question about a year ago, and forgot about it. Interestingly I must have retained a bit of the answer, as I referred the LINEST function in the help facility. What I didn't know is that the HELP is actually anti-HELP, 'cause the first line of LINEST is

Calculates the statistics for a line by using the "least squares" method to
calculate a straight line that best fits your data, and returns an array that
describes the line.

.... which serves to convince all but the most ornery user that LINEST is not suitable for non-linear functions. Who'd read that and expect that it would work for multi-order polynomials!

Anyway, thanks for the reply, and guidance. It works well. I am using Excel 2000 though, so I'll have to see if I can find out any info about HOW off the algorithm is for the line fit. Hopefully i
is pretty close or it will mess up the temperature calibrations I'm doing for a product

Thx again

Ross

----- Jerry W. Lewis wrote: ----

Assuming that B7:B29 is the y data and C7:C29 is the x data select three
adjacent cells in the same row and us
=LINEST(B7:B29,C7:C29^{1,2}
array entered (Ctrl-Shift-Enter)

Alternately, David Braden has posted VBA code to extract the
coefficients directly from the chart into cell

http://groups.google.com/[email protected]

The advantage of using the chart coefficients (either manually or via
Braden's code) is that LINEST in Excel versions prior to 2003 uses a
numerically poor algorithm that can give inaccurate results with some
data sets. The chart trendline (extracted by Braden's code) is much
better numericall

Jerr

RGK wrote

 

[Jerry W. Lewis]

You are correct, that Help line is just plain wrong. LINEST works here,
because it solves linear estimation problems, and polynomials are linear
in the unknown coefficients (even though the function is not a straight
line). The help could be much clearer on this.

Glad to help,
Jerry