to find trend line with the highest R Square value

[darbary]

When one has a collection of Data in a Column, we need to make chart of it, then manually tryout different trendlines (Linear, Exponential, Polynomial, etc.) which give the highest value of R Square. (There are programmes including freeware, that does it.)


Is there a way in EXCEL, wherby it finds the trendline having the highest "R square" Value?

 

[Luke M]

I would think a polynomial of 6th order would give you the higest R-square, as you are allowing the line to make the most amount of "bends". For a formula, you might try starting with the RSQ function. I don't do too much with the statistical functions, but it looks close to what you are wanting.

 

[Hui]

Darbary


I think you have a couple of choices


Charts

You can setup a series of charts and apply the same data to each

Then add a Trendline to each

Change the Trendline Type for each to use the different types of estimation techniques

Display the parameters on the Charts

Excel automatically determines the best fit for each trend line type which means highest R^2 value for the trend type


You will manually have to look at the R^2 values


Linest() Function

You can setup a series of Linest functions to model the various Trendlines in an equation

Then you can use a Formula to retrieve the R^2 coefficient from the Linest Array

Once again Excel will automatically determine the highest R^2 value when determining the linest coefficients for each Trendline type


You may want to have a read of the Linest() function here: http://chandoo.org/wp/2011/01/26/trendlines-and-forecasting-in-excel-part-2/


Having Excel formulas automatically retrieve the R^2 values will allow you to automatically retrieve the type with the highest R^2 value