• July 17, 2017
    Announcement: New site design for Microsoft Community

    In July, Microsoft will roll out the first of ongoing site improvements aimed to modernize Microsoft Community and help customers get the most out of their community experience.

    • During the roll out period, you may see the old or new site design depending on your location

    • We expect the roll out to finish by 31 July

    Note: Past private message conversations will not move to the new site design. Please save any private messages you would like to keep.

     Learn more about the upcoming site improvements in this thread.

    Thank you for being part of Microsoft Community!


Excel R squared is Incorrect

RoryVan Tuyl asked on

Excel computes R2 (r squared) incorrectly for the case where a quadratic fit is obtained through a set of data scattered about a parabola.

The correct value of R squared for this case should alway be near zero, due to the definition of R squared.

This raises an important question: how does Excel calculate R squared in the first place?

This result was obtained using the curve fitting feature of a graph in Excel Office 12.

9 people had this question

Abuse history

The answered status icon Answer
RoryVan Tuyl replied on

So as it turns out, the only problem here is one of communication.

Excel Charts display "R^2" along with the equation of the best fit curve.

I had assumed that this referred to the R^2 of Y vs X, just as the best fit curve refers to Y vs X

But in fact, it refers to the R^2 of Y vs f(X), where f(X) is the best fit function

There should be some clarification in the definition of R^2 and/or the documentation of curve fitting to make users aware of this peculiarity. The numbers are correct, but what they mean was not clear to me, nor would it be to many users.

Thank you for taking this issue seriously. I appreciate your work.

Rory Van Tuyl

3 people found this helpful

Abuse history