NonLinear R-squared (R2)

[Guest]

I know for linear trend lines I can use the RSQ function (e.g.
=RSQ(B1:B4,A1:A4)) to calculate R-squared, but is it possible to calculate
R-squared for nonlinear trend lines (i.e. logarithmic, polynomial, power,
exponential) ?

Thank you.

 

[Guest]

Bio --

The easiest way is to create the XY graph. Then add a trendline
(right-click on one of the data points in the graph), select the type of
trendline, then select the options for the trendline formula and R-squared.

HTH

 

[Mike Middleton]

Biocellguy -

If you need R^2 from worksheet formulas (instead of from the trendline
Options of an XY chart), you could use the transformations that John
Walkenbach shows at
http://www.j-walk.com/ss/excel/tips/tip101.htm
Instead of using the INDEX function suggested by John Walkenbach, you could
select a large range and array-enter (Control+Shift+Enter) the LINEST
function to obtain an R^2 result. See Excel's Help for the LINEST function.

Or, you could use the transformations as arguments for the RSQ function. For
example, for the Logarithmic trendline, you would use
=RSQ(y-range,LN(x-range))
See John Walkenbach's list for the other transformations.

- Mike
www.MikeMiddleton.com