NonLinear R-squared (R2)
 

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.

Answer: NonLinear R-squared (R2)
 

Yes, it is possible to calculate R-squared for nonlinear trend lines in Excel. Here are the steps to do so:

1. First, create a scatter plot of your data with the nonlinear trend line added. To do this, select your data and go to the "Insert" tab on the ribbon. Click on "Scatter" and choose the type of scatter plot you want to create.
2. Once you have your scatter plot, right-click on the trend line and select "Add Trendline". In the "Format Trendline" pane that appears on the right side of the screen, choose the type of nonlinear trend line you want to add (e.g. logarithmic, polynomial, power, exponential).
3. After you have added the trend line, right-click on it again and select "Format Trendline". In the "Format Trendline" pane, click on the "Options" tab and check the box next to "Display R-squared value on chart".
4. The R-squared value for your nonlinear trend line will now be displayed on the chart. You can also use the R-squared formula to calculate it manually. To do this, use the following formula:
   Formula:

   ---

   =1-SSresid/SStotal 


   ---

   , where SSresid is the sum of the squared residuals and SStotal is the total sum of squares. You can find these values by using the LINEST function in Excel.
5. To use the LINEST function, select a range of cells where you want to output the results, then enter the following formula:
   Formula:

   ---

   =LINEST(y-values,x-values^{1,2,3...n},TRUE,FALSE) 


   ---

   , where y-values is the range of dependent variable data, x-values is the range of independent variable data, and n is the order of the polynomial (if using a polynomial trend line).
6. The LINEST function will output an array of values, including the sum of squared residuals and the total sum of squares. Use these values to calculate R-squared using the formula in step 4.

That's it! You should now have the R-squared value for your nonlinear trend line in Excel.

NonLinear R-squared (R2)
 

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

"Biocellguy" wrote:

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.

NonLinear R-squared (R2)
 

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

"Biocellguy" wrote in message
...
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.