3
$\begingroup$

I have been using CORREL() function in MS Excel for a considerable time. I just wanted semi-manually to calculate correlation for fun, but the value I got did not match the value I'am getting from CORREL().

Here is my data:

x
18787
27769
32330
7832
27815
26075
98294
31807

y
329
371
371
112
267
332
386
379

Excel formula for correlation = CORREL(X, Y) = 0.528

And to validate I used the Pearson's product-moment coefficient = COVAR(X,Y) / (STDEV(X)*STDEV(Y)) = 0.46207

Apparently there is a significant difference between the results.

$\endgroup$
  • $\begingroup$ Use STDEVP instead of STDEV. $\endgroup$ – whuber Apr 2 '14 at 18:29
  • $\begingroup$ I tried STDEVP (and STDEV.P.) But still doesn't match the correl() result. $\endgroup$ – uha1 Apr 2 '14 at 19:22
  • $\begingroup$ On the contrary, using STDEVP does solve the problem: I tested this out before posting my comment and I also confirmed it by checking the online help for COVAR. $\endgroup$ – whuber Apr 2 '14 at 20:58
  • $\begingroup$ just multiply the covariance that excel gives you by n , then divide by n-1 to get the correct answer $\endgroup$ – joe Mar 29 '15 at 7:36
4
$\begingroup$

Assuming you have a recent version of Excel, you need to use covariance.s(). Covariance.p() is equivalent to covar(), which is deprecated.

If you look at the help files for the functions, you can see that covar() uses n as the denominator, not n-1.

(Excel contains a number of 'gotchas' such as this - you'd be better off with something designed for statistics - such as R.)

$\endgroup$
  • $\begingroup$ Great! Using covariance.s() and also stdev.s() solves the issue. Thanks very much! $\endgroup$ – uha1 Apr 2 '14 at 20:31
  • 2
    $\begingroup$ And it is quite strange that excel has a different degrees of freedom for covariance(). I wondered if they had an logic for that. or its really just a mistake. Apparently, R works smoothly in the same case. cov(x,y)/(sd(x)*sd(y)). Thanks again! $\endgroup$ – uha1 Apr 2 '14 at 20:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.