Share via

Trendline R2 Value

Anonymous
2020-11-10T09:33:42+00:00

Hi All,

I have some Excel users that have asked be a good question.

In Excel the have a XY (Scatter) graph. On this they have a Trendline where the R-squared value is show with "Set Intercept" of 0.0.

Now the problem is that the value is not the same for all users. Users with older builds of Excel 2002 for example get the correct value of -0,209

But never versions of Excel (2008 in this example) get it wrong:

I'm not strong in the math behind. But find it strange that the old version get it correct.

Any ideas?

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

6 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2020-11-11T06:35:36+00:00

    Thank you all so much for your great insight.

    I have taken all your findings back to the team. And they are looking in to it :)

    Was this answer helpful?

    0 comments
  2. Anonymous
    2020-11-11T05:14:15+00:00

    I believe the newer version is correct.

    When the sum of your residuals is greater (the numerator), then the value will be negative...

    You don't really need the prediction column if you factor out the slope:

    Was this answer helpful?

    0 comments
  3. Deleted

    This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

    Comments have been turned off. Learn more

  4. Anonymous
    2020-11-10T12:12:26+00:00

    Hi Jazlyn,

    Sure thing. Here are the versions:

    I have shared the test file here:

    https://1drv.ms/x/s!AlRav\_mZ7hVFkKw6FlMW7FRcN\_nOoA?e=tPs62j

    Was this answer helpful?

    0 comments
  5. Anonymous
    2020-11-10T11:53:34+00:00

    Hi Thomas,

    Sorry for the inconvenience the problem caused for you.

    Based on your description, the R-squared shows different values for the same chart data in different versions of Excel. I tested it on version 2008 (Build 13127.20638) Semi-annual Enterprise Channel (Preview) and version 2002 (Build 12527.21236) Semi-annual Enterprise Channel, however, there was no problem for the R-squared value of the same data.

    To help you better, please provide more information for us:

    1. Screenshots of these two Office version(open Excel > File > Account > capture a screenshot of the Product Information column)
    2. If you create a new file with new data, will the same problem happen?

    Additionally, could you please send a sample for us via OneDrive link so that we can also investigate it for you.

    Best regards,

    Jazlyn

    Was this answer helpful?

    0 comments