[R] Variance is different in R vs. Excel?

[(Ted Harding)]

[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-bounces at r-project.org] On Behalf Of Karl Fetter
> Sent: Monday, February 9, 2015 3:33 PM
> To: r-help at 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.Harding at wlandres.net>
Date: 09-Feb-2015  Time: 22:15:44
This message was sent by XFMail