CORREL versus r-squared

[Shell]

Why do I get different values for the same arrays when I
run CORREL and ad r-squared value to a scatter plot?

 

[Michael R Middleton]

Shell -

Why do I get different values for the same arrays when I run CORREL and ad
r-squared value to a scatter plot? <

First, CORREL is R, and R-squared from Add Trendline (or from worksheet
function RSQ) is R*R.

Second, as I recall, depending on the version of Excel, there may or may not
be an adjustment for the number of observations (degrees of freedom), but
maybe that was only for COVAR.

Third, adjusted R square, from the Regression tool of the Analysis ToolPak,
also adjusts for the number of explanatory X variables.

- Mike
www.mikemiddleton.com

 

[Jerry W. Lewis]

Michael R Middleton wrote:
....

Second, as I recall, depending on the version of Excel, there may or may not
be an adjustment for the number of observations (degrees of freedom), but
maybe that was only for COVAR.

....


I do not recall that. As best I recall, CORREL has used a numerically
stable 2-pass algorithm in all versions, where RSQ and PEARSON only
switched to that in 2003. Prior to 2003, the R-squared value from
LINEST was wrong if no intercept was fit.

Jerry

 

[Michael R Middleton]

Jerry -

Yes, CORREL has not changed.

After further recollection and investigation, I realize that my faulty
recall was related only to differences in COVAR and the Analysis ToolPak's
Covariance tool.

- Mike