# Validating Correlation in Excel

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.

• Use STDEVP instead of STDEV. – whuber Apr 2 '14 at 18:29
• I tried STDEVP (and STDEV.P.) But still doesn't match the correl() result. – uha1 Apr 2 '14 at 19:22
• 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. – whuber Apr 2 '14 at 20:58
• just multiply the covariance that excel gives you by n , then divide by n-1 to get the correct answer – joe Mar 29 '15 at 7:36

• Great! Using covariance.s() and also stdev.s() solves the issue. Thanks very much! – uha1 Apr 2 '14 at 20:31