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.

8 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

1 person found this helpful

Abuse history