Coefficients of Quadratic and Log Fits?

[Kevin Sprinkel]

I've set up a worksheet where a user enters up to six data
points (x,y). Next to them, I show three plots of the
data, with a linear, quadratic, and logarithmic curve fit.

I would like the user to enter a new test point (x value)
and have the three interpolated results show. The user
can, by looking at the plots, decide which best fits the
data and hence which result to use.

I can automatically calculate the linear result using the
Slope and Intercept functions, but I've been stuck so far
by manually entering the quadratic and logarithmic
coefficients after they show up on the plots.

Are there any functions available that will return these
coefficients from the known x's and y's?

TIA
Kevin Sprinkel

 

[Jerry W. Lewis]

Use LINEST() and LOGEST() or TREND() and GROWTH(). Alternately, David
Braden has posted VBA code to extract the coefficients directly from the
chart into cells

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

The choice would depend on what version of Excel you are running. In
versions prior to 2003, LINEST/LOGEST/TREND/GROWTH used numerically
inferior algorithms that could give wrong answers in numerically
challenging situations, while the chart trendline algorithms have always
been excellent.

If you have 2003, download the most recent hotfixes.

Don't be mislead by HELP for LINEST/LOGEST/TREND/GROWTH, they do handle
multiple linear (in unknowns) regression, such as polynomials. If the
data are in columns, you can fit a quadratic as

=LINEST(y,x^{1,2})

Replace {1,2} with {1;2} if the data are in rows.

Jerry

 

[Kevin Sprinkel]

Thanks, Jerry. I've downloaded David's code and look
forward to using it.

Best regards.

Kevin Sprinkel