Squared Correlation Coefficient on linear plot different values in different versions of Excel

Question

I've encountered an issue with R2 value of linear trendline on a graph when opened upon different versions of Excel.

The graph is plotted as a scatter graph, with a set intercept (0,0) -this is important as the versions tested seem to agree when not forced through 0.

Excel 365 (Version 1908) and Excel 2016: R2 = 0.9992

Opening the exact same file in Excel Microsoft 365 Version 2008, or on the web version of excel gives the R2 on the graph as R2 0.9998!

For Reference same filed opened on web:

Both versions of Excel 365 and the 2016 version give an R2 of 0.9993 when not forced through 0. 

Which is correct? Why is there a difference?

Answer 1

Which specific functions are you using?

MS sometimes updates functions. Usually they give them new names, but rarely they don't. This could lead to different results.

.

I've checked my notes and have not found anything yet. If you tell me/us the specific functions that would narrow the search.

Answer 2

In the cases above I'm generating a scatter chart from the data and adding a linear tendline, setting intercept to 0,0 and displaying the formula and R2 value on the graph, rather than using any particular functions. 

I did come across Incorrect output is returned when you use the Linear Regression (LINEST) function in Excel (microsoft.com) - however that article isn't dated or which versions it references (other than 2007).  Case 2 applies, but simply not putting the trendline through 0 isn't an option!

If I run the Linest function and regression functions with intercept at 0 on the Excel 2016, I get same results as the r2 value displayed on the graphs in the newer version of excel (r2 = 0.9998).

Answer 3

OK, instead of specifying "0,0", make the coordinates very small, (0.00000000001, 0.000000001) or some such small value approaching zero? 

I did find an article saying Linest() was one of the functions that were retroactively updated to return arrays as part of the Dynamic Array farrago.  Maybe part of that change caused your problem resulting in the suggested work around, which you don't want / can't use.

<edit>

Sorry, forgot to include the link

@ Excel functions that return ranges or arrays
https://support.microsoft.com/en-us/office/excel-functions-that-return-ranges-or-arrays-7d1970e2-cbaa-4279-b59c-b9dd3900fc69
In September, 2018 we announced that Dynamic Array support would be coming to Excel. This allows formulas to spill across multiple cells if the formula returns multi-cell ranges or arrays. This new dynamic array behavior can also affect earlier functions that have the ability to return a multi-cell range or array.
Below is a list of functions that could return multi-cell ranges or arrays in what we refer to as pre-dynamic array Excel. If these functions were used in workbooks predating dynamic arrays, and returned a multi-cell range or array to the grid (or a function that did not expect them), then silent implicit intersection would have occurred. Dynamic array Excel indicates where implicit intersection could occur using the @ operator, and as a result, these functions may be prepended with an @ if they were originally authored in a pre-dynamic array version of Excel. Additionally, if authored on dynamic array Excel, these functions may appear as legacy array formulas in pre-dynamic array Excel unless prepended with @.