COVAR

[Dan]

I am having trouble reproducing the values produced by the
Data Analysis tool that calculates a variance-covariance
matrix. When I try to calculate one cell of the matrix by
doing this: COVAR(X1,X2), where X1 & X2 are arrays
(columns) of data in a data table, I get a different
result than the covariance calculated by the Data Analysis
Tool. Does anyone out there know what I am doing wrong?

Thanks,

Dan

 

[Michael R Middleton]

Dan -

I am having trouble reproducing the values produced by the Data Analysis

tool that calculates a variance-covariance matrix. When I try to calculate
one cell of the matrix by doing this: COVAR(X1,X2), where X1 & X2 are arrays
(columns) of data in a data table, I get a different result than the
covariance calculated by the Data Analysis Tool. Does anyone out there know
what I am doing wrong? <

You did not specify which version of Excel you are using.

In some versions of Excel the Covariance analysis tool uses n-1 in the
denominator (sample covariance) and the COVARIANCE worksheet function uses n
(population covariance).

- Mike Middleton, www.usfca.edu/~middleton

 

[Dan]

-----Original Message-----
Dan -
the Data Analysis
tool that calculates a variance-covariance matrix. When I try to calculate
one cell of the matrix by doing this: COVAR(X1,X2), where X1 & X2 are arrays
(columns) of data in a data table, I get a different result than the
covariance calculated by the Data Analysis Tool. Does anyone out there know
what I am doing wrong? <

You did not specify which version of Excel you are using.

In some versions of Excel the Covariance analysis tool uses n-1 in the
denominator (sample covariance) and the COVARIANCE worksheet function uses n
(population covariance).

- Mike Middleton, www.usfca.edu/~middleton


.

Thanks, Mike! I am using Excel 97 SR-2. Is there a way,
without totally rebuilding the calculation, that I might
be able to modify the COVAR worksheet function?

 

[Michael R Middleton]

Dan -
< <

I am using Excel 97 SR-2. Is there a way, without totally rebuilding the

calculation, that I might be able to modify the COVAR worksheet function? <

If you want sample covariance in a worksheet cell, you could use
=COVAR(X1,X2)*n/(n-1) substituting numbers for n.

Or, if the arrays may have different sizes and you want dynamic changes, you
could use =COVAR(X1,X2)*COUNT(X1)/(COUNT(X1)-1).

- Mike Middleton, www.usfca.edu/~middleton