[R] R vs. Excel (R-squared)
pburns at pburns.seanet.com
Tue Jan 24 19:45:46 CET 2006
I would think that a negative value for a statistic that takes
on non-negative values would be suspiciously low. But
some people think I'm overly skeptical.
There may or may not be a discussion of this in one or more
of the links in 'Spreadsheet Addiction'.
The executive summary is, "Don't do statistics in spreadsheets,
patrick at burns-stat.com
+44 (0)20 8525 0696
(home of S Poetry and "A Guide for the Unwilling S User")
Lance Westerhoff wrote:
>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!
>To begin, I have a set of data points in which the y is the
>experimental number and x is the predicted value. The Excel-
>generated graph (complete with R^2 and trend line) is provided at
>this link if you want to take a look:
>As you can see, the R-squared that is reported by Excel is -0.1005.
>Now when I bring the same data into R, I get an R-square of +0.9331
>(see below). Being that I am new to R and semi-new to stats, is
>there a difference between "multiple R-squared" and R-squared that
>perhaps I am simply interpreting this wrong, or is this a known
>inconsistency between the two applications? If so, which is
>correct? Any insight would be greatly appreciated!
> > # note: a is experimental and c is predicted
> > summary(lm(a~c-1))
>lm(formula = a ~ c - 1)
> Min 1Q Median 3Q Max
>-2987.6 -1126.6 -181.7 855.3 5602.8
> Estimate Std. Error t value Pr(>|t|)
>c 0.99999 0.01402 71.33 <2e-16 ***
>Signif. codes: 0 ‘***’ 0.001 ‘**’ 0.01 ‘*’ 0.05 ‘.’ 0.1 ‘ ’ 1
>Residual standard error: 1423 on 365 degrees of freedom
>Multiple R-Squared: 0.9331, Adjusted R-squared: 0.9329
>F-statistic: 5088 on 1 and 365 DF, p-value: < 2.2e-16
> > version
>system powerpc, darwin7.9.0
>svn rev 36812
>Thank you very much for your time!
>Lance M. Westerhoff, Ph.D.
>Email: lance at quantumbioinc.com
>"Safety is not the most important thing. I know this sounds like heresy,
>but it is a truth that must be embraced in order to do exploration.
>The most important thing is to actually go." ~ James Cameron
>R-help at stat.math.ethz.ch mailing list
>PLEASE do read the posting guide! http://www.R-project.org/posting-guide.html
More information about the R-help