Same XY scatter but different R square

Tying up loose ends for posterity....

linnan5945  wrote: ``In my computer trying Office 2013, 2016, and 2019, all the R square of the linear trend is 0.9886, while in my friends' computers, it's 0.9945. We used SAS and SPSS to calculate, which also showed R2=0.9945. We set the intercept = 0.``

I wrote: ``the [coefficient of determination] wikipage (click here) explains that there "are several definitions of R^2 that are only sometimes equivalent". One such difference is the calculation of the "correlation coefficient" (r) v. "coefficient of determination" (R).``

That is, indeed, the reason for the difference, to a degree.

Excel RSQ calculates the square of the Pearson correlation coefficient (Excel PEARSON).

But LINEST R^2 and the linear trendline R^2 are based on the coefficient of determination (CoD).

When "zero intercept" is not specified -- that is, when there is a non-zero intercept -- all 3 statistics are the same, within the limits of floating-point arithmetic.

But when "zero intercept" is specified -- that is, when there is no intercept -- CoD differs from the Excel RSQ.

And more to the point, there is some debate among statisticians about how to calculate the CoD when a zero intercept is specified.

First, note that LINEST R^2 and the linear trendline R^2 are __not__ the square of any number, whereas Excel RSQ is. "R^2" is simply the notation that statisticians use for the CoD. I will use the term "CoD" (duh!) to avoid confusion.

The distinction is significant because the general method of calculating the CoD can result in __negative__ values for "R^2", whereas Excel RSQ is always non-negative.

The negative "R^2" (CoD) might seem like an error, because the square of any real number cannot be negative. But it is not an error, for the very reason that the CoD is not the square of any number.

Nevertheless, some statiscians justify the alternative method of calculating the CoD, in part, in order to avoid the negative CoD.

The method of calculating Excel RSQ is described in the RSQ help page (click here).

The methods of calculating the CoD is described in the LINEST help page (click here)

In general, the CoD is calculated by the formula 1 - SSres/SStot, or equivalently SSreg/SStot. SStot is Sigma((Y - avgY)^2), where "Y" is the original data.

That formula is always used to calculate the linear trendline R^2, at least in Excel 2010 (which I use) and in 2013, 2016 and 2019 (according to you).

That formula is also used to calculate LINEST R^2 when "zero intercept" is not specified (const = TRUE).

However, when "zero intercept" is specified (const = FALSE), LINEST calculates SStot differently, namely: Sigma(Y^2).

An explanation of that formula can be found in an academic article (click here), which is a heavy read (i.e. TMI).

That is why LINEST R^2 and the linear trendline R^2 differ in Excel 2010, 2013, 2016 and 2019, among others(?).

However, I am told that in Office 365 Excel, the calculation of the linear trendline R^2 has been changed to agree with LINEST R^2 when "zero intercept" is specified.

So for Office 365 users, the two values of "R^2" (CoD) should be the same, within the limits of floating-point arithmetic.

-----

From the "coefficient of determination" wikipage....

From the LINEST help page ....

From the RSQ help page....