[See at end] On 09-Feb-2015 21:45:11 David L Carlson wrote: > Time for a new version of Excel? I cannot duplicate your results in Excel > 2013. > > R: >> apply(dat, 2, var) > [1] 21290.80 24748.75 > > Excel 2013: > =VAR.S(A2:A21) =VAR.S(B2:B21) > 21290.8 24748.74737 > > ------------------------------------- > David L Carlson > Department of Anthropology > Texas A&M University > College Station, TX 77840-4352 > > > -----Original Message----- > From: R-help [mailto:r-help-boun...@r-project.org] On Behalf Of Karl Fetter > Sent: Monday, February 9, 2015 3:33 PM > To: r-help@r-project.org > Subject: [R] Variance is different in R vs. Excel? > > Hello everyone, I have a simple question. when I use the var() function in > R to find a variance, it differs greatly from the variance found in excel > using the =VAR.S function. Any explanations on what those two functions are > actually doing? > > Here is the data and the results: > > dat<-matrix(c(402,908,553,522,627,1040,756,679,806,711,713,734,683,790,597,872 > ,476,1026,423,476,419,591,376,640,550,601,588,499,646,693,351,730,632,707,779, > 838,814,771,533,818), > nrow=20, ncol=2, byrow=T) > > var(dat[,1]) >#21290.8 > > var(dat[,2]) >#24748.75 > >#in Excel, the variance of dat[,1] = 44763.91; for dat[,2] = 52034.2 > > Thanks, > Karl
I suspect that something has happened to the reading-in of the data into Excel. (I don't know much about Excel, and that's because I don't want to ... ). The ratio of the variances of the two datasets in R is: var(dat[,2])/var(dat[,1]) # [1] 1.162415 while the ratio of th results from Excel is: 52034.2/44763.91 # [1] 1.162414 so they are almost identical. So it is as if Excel was evaluating the variances for data which are sqrt(44763.91/var(dat[,1])) # [1] 1.45 sqrt(52034.2/var(dat[,2])) # [1] 1.449999 times the data used by R. So maybe there's a "nasty" lurking somewhere in the spreadsheet? (Excel is notorious for planting things invisibly in its spreadsheets which lead to messed-up results for no apparent reasion ... ). Hoping this helps, Ted. ------------------------------------------------- E-Mail: (Ted Harding) <ted.hard...@wlandres.net> Date: 09-Feb-2015 Time: 22:15:44 This message was sent by XFMail ______________________________________________ R-help@r-project.org mailing list -- To UNSUBSCRIBE and more, see https://stat.ethz.ch/mailman/listinfo/r-help PLEASE do read the posting guide http://www.R-project.org/posting-guide.html and provide commented, minimal, self-contained, reproducible code.
