Excel for dummies - Covariance/Correlation question

S

Schizoid Man

I have the following two arrays

A B
0.656762726 0.502636772
0.607062936 0.358819486
0.176421847 0.177496945
0.850282971 0.408820642
0.840389619 0.238850111
0.677780042 0.95826463
0.689123518 0.32826283
0.09339563 0.080583459
0.449812814 0.62147784

The covariance is 0.025759373 and the standard deviations are a:
0.269721719, b: 0.263401137.

I would expect the correlation to be Cov(a,b) / (Stdev(a) * Stdev(b)) =
0.362578234

However, Excel calculates the correlation as 0.407900513.

Please educate me.

Thanks,
Schiz
 
B

Bernard Liengme

Note that 0.407900513/0.362578234 = 1.125
And 9/8 = 1.125
Now look at the formulas in Help for COVAR, STDEV and CORREL and you will
see where the 9/8 (or n/(n-1) ) comes from.
Had you used STDEVP (population std dev) the agreement would have been
perfect.
best wishes
 
Top