R2 value inconsistent between graph and linest function

[LML]

For a data correlation, I have added a trendline to the plotted data with R2, and also used the linest function on the same data.

The two R2 values match when there is an intercept, but when the correlation is forced through the origin the R2 values differ by up to 2%.

Any ideas why this is? I'm inclined to believe the graph R2 as this is slightly lower when forced through the origin, whereas the linest R2 is higher.

Cheers

 

[pgc01]

Hi LML
Welcome to the board

Please always post you version of excel (or add it to your signature).

If your excel version if prior to excel 2003 then this is a known bug.

The intercept argument should be set to FALSE only if you want to force the regression line to go through the origin. For Excel 2002 and for earlier versions of Excel, setting this argument to FALSE always returns results that are not correct, at least in the detailed statistics that are available from LINEST.

Check here

http://support.microsoft.com/kb/828533

 

[LML]

Thanks for that - I am using excel 2007, although the spreadsheet was set up a few years ago if that makes any difference.
Cheers

 

[Louise A]

Hi there,

I have the same problem and I'm using excel 2010. I don't know if there's a simpler way to do what I'm doing, but I'm running through data, finding the r^2 value and if it is greater than 0.99 then i ask it to plot the graph. The only thing is that it is not picking up the correct r^2 value, any help would be much appreciated

Louise

 

[mirabeau]

Hi there,

I have the same problem and I'm using excel 2010. I don't know if there's a simpler way to do what I'm doing, but I'm running through data, finding the r^2 value and if it is greater than 0.99 then i ask it to plot the graph. The only thing is that it is not picking up the correct r^2 value, any help would be much appreciated

Louise

To check on your r^2, if you only have the 2 variables, say X and Y, you can use the square of the Excel =correl() function.

Otherwise, there's a regression facility in the Excel data analysis (in Data ribbon in Excel 2007) that will give the R^2 for more general regressions.

How do you know that your method (Linest?) is not picking up the correct r^2?

 

[Louise A]

Thank you for your reply. I know it's not correct because when I plot the data on a graph and ask for the trendline and then set the intercept to 0 I get one value for r^2, when I use the linest function I get another and while the gradients are identical, the r^2 values are not. I have used the same cells for each.

 

[pgc01]

Thank you for your reply. I know it's not correct because when I plot the data on a graph and ask for the trendline and then set the intercept to 0 I get one value for r^2, when I use the linest function I get another and while the gradients are identical, the r^2 values are not. I have used the same cells for each.

Hi

You should get exactly the same value displayed on the chart and using Linest().

Can you post a sample of your data with the values you get on the chart and using Linest()?

 

[Louise A]

I've tried downloading the html maker file, but my work computer won't let me. Is there another way?

 

[Louise A]

Ok, I ravaged my brain for information that I learnt during A-level and vaguely remembered regression and calculating the statistcs of things, so did it by hand. The calculation matched the value on the graph. I shall just use this value, as long winded as it is, it's the more accurate way in my opinion.

 

[pgc01]

Ok. In case you want to post some data, you don't need anything fancy. Just 2 columns separated by spaces would be enough. Also, in most cases you can copy a table directly from excel.