Excel R squared is Incorrect

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.


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

2 people found this reply helpful


Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.


Question Info

Last updated April 16, 2021 Views 7,080 Applies to: