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.

 

Question Info

Last updated July 18, 2019 Views 6,697 Applies to:
Answer
Answer

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 was helped by this reply

·

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

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

How satisfied are you with this response?

Thanks for your feedback.