Best fit Curve

[Guest]

Is there a program that will take data in rows and columns and return the
trendline equation? What I have in mind is similar to LINEST but would go
though all the possilbe curves (power, exponential, poly, etc) automatically
and return the best fit based on the highest R squared value. Currently, I
have to manually plot the data add a trendline and determine which is the
best fit.
Thanks,
Nick

 

[Guest]

You'd probably have to write up a macro to do that.

That said, i know what you mean, since i have to do that a lot as well.
Although from a statistics point of view, it's not the most ideal thing to do
since chasing R squared isn't how most modelling is done.

I'd be interested in any solution as well. I know that you can write a macro
to do this, by using the ML solutions as the formulas for most of the
equation types to then choose the best one.

 

[Guest]

If you have n data points, a polynomial of degree n-1 will by definition have
R-squared equal to 1, but will likely be totally worthless as a predictor for
any other points on the curve. A better criteria would be "Adjusted
R-squared", which includes a penalty for inflating the number of parameters
http://en.wikipedia.org/wiki/Coefficient_of_determination#Adjusted_R2
The ATP regression tool calculates adjusted R-squared, provided that data is
in columns and you include an intercept (the ATP calculation for both
R-squared and adjusted R-squared is wrong if data is in rows or if there is
no intercept).

All the chart trendlines can be computed in LINEST() (which can be used from
VBA), although some of them would require transformation of either the y or x
values.

If you would rather not go to VBA, you can use the INDEX() function to pick
off the R-squared value from LINEST. Prior to Excel 2003, the LINEST
R-squared value is wrong if there is no intercept.

Jerry