Question

Q: 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.

Answer

A:

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

Did this solve your problem?

Sorry this didn't help.



 
Question Info

Views: 6,262 Last updated: June 16, 2018 Applies to: