CORREL versus COVAR/STDEV^2.......why different?

[Mike]

Hi everyone,

If you list a column of any data, try to calculate CORREL for same
column (i.e., against itself), you get 1 (which is correct).

Now, try to calculate COVAR for that column (also against itself), and
STDEV. then try to get Correlation using COVAR/(STDEV^2) you don't get
1?!

What is the problem here? Shouldn't both get you 1?

Thanks,
Mike

 

[Guest]

Look at the formulas in Help for these functions. Mathematically,
COVAR/STDEV^2 should give you (n-1)/n = (1-1/n), which is what I get with the
test cases that I tried.

If you use STDEVP instead of STDEV, you should get the results that you
expected.

Jerry

 

[Mike]

Yes, thanks Jerry...

Look at the formulas in Help for these functions. Mathematically,
COVAR/STDEV^2 should give you (n-1)/n = (1-1/n), which is what I get with the
test cases that I tried.

If you use STDEVP instead of STDEV, you should get the results that you
expected.

Jerry