[R] R vs. Excel (R-squared)

[Lance Westerhoff]

Hi-

On Jan 24, 2006, at 12:08 PM, Peter Dalgaard wrote:

> Lance Westerhoff <lance at quantumbioinc.com> writes:
>
>> Hello All-
>>
>> I found an inconsistency between the R-squared reported in Excel vs.
>> that in R, and I am wondering which (if any) may be correct and if
>> this is a known issue.  While it certainly wouldn't surprise me if
>> Excel is just flat out wrong, I just want to make sure since the R-
>> squared reported in R seems surprisingly high.  Please let me know if
>> this is the wrong list.  Thanks!
>
> Excel is flat out wrong. As the name implies, R-squared values cannot
> be less than zero (adjusted R-squared can, but I wouldn't think
> that is what Excel does).

I had thought the same thing, but then I came across the following  
site which states: "Note that it is possible to get a negative R- 
square for equations that do not contain a constant term. If R-square  
is defined as the proportion of variance explained by the fit, and if  
the fit is actually worse than just fitting a horizontal line, then R- 
square is negative. In this case, R-square cannot be interpreted as  
the square of a correlation." Since

R^2 = 1 - (SSE/SST)

I guess you can have SSE > SST which would result in a R^2 of less  
then 1.0.  However, it still seems very strange which made me wonder  
what is going on in Excel needless to say!

http://www.mathworks.com/access/helpdesk/help/toolbox/curvefit/ 
ch_fitt9.html

-Lance