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

[Peter Dalgaard]

Lance Westerhoff <lance at quantumbioinc.com> writes:

> 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

Well, one more program (Matlab) decided to do things contrary to
conventional statistics programs it seems....


-- 
   O__  ---- Peter Dalgaard             Øster Farimagsgade 5, Entr.B
  c/ /'_ --- Dept. of Biostatistics     PO Box 2099, 1014 Cph. K
 (*) \(*) -- University of Copenhagen   Denmark          Ph:  (+45) 35327918
~~~~~~~~~~ - (p.dalgaard at biostat.ku.dk)                  FAX: (+45) 35327907