[R] R vs. Excel (R-squared)
Patrick Burns
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'.
http://www.burns-stat.com/pages/Tutor/spreadsheet_addiction.html
The executive summary is, "Don't do statistics in spreadsheets,
especially Excel."
Patrick Burns
patrick at burns-stat.com
+44 (0)20 8525 0696
http://www.burns-stat.com
(home of S Poetry and "A Guide for the Unwilling S User")
Lance Westerhoff wrote:
>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!
>
>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:
>
>http://www.quantumbioinc.com/downloads/public/excel.png
>
>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))
>
>Call:
>lm(formula = a ~ c - 1)
>
>Residuals:
> Min 1Q Median 3Q Max
>-2987.6 -1126.6 -181.7 855.3 5602.8
>
>Coefficients:
> 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
> _
>platform powerpc-apple-darwin7.9.0
>arch powerpc
>os darwin7.9.0
>system powerpc, darwin7.9.0
>status
>major 2
>minor 2.1
>year 2005
>month 12
>day 20
>svn rev 36812
>language R
>
>======================
>
>
>Thank you very much for your time!
>
>-Lance
>____________________
>Lance M. Westerhoff, Ph.D.
>General Manager
>QuantumBio Inc.
>
>WWW: http://www.quantumbioinc.com
>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
>https://stat.ethz.ch/mailman/listinfo/r-help
>PLEASE do read the posting guide! http://www.R-project.org/posting-guide.html
>
>
>
>
>
More information about the R-help
mailing list