How to Calculate Covariance in Excel 2007

By Dan Howard

Excel 2007 includes many functions that you can use to calculate the statistical properties of arrays of data. One such statistical measure is covariance, a measure of the degree to which two variables change in unison; two variables that are highly dependent on one another have high covariance, while two variables that are independent have a covariance of zero. Use Excel's COVAR function to calculate the covariance of a pair of variables that have an equal number of data points.

Step 1

Run Microsoft Excel 2007 and open the workbook that contains your data arrays.

Step 2

Determine the cell addresses for your two arrays. For example, if your arrays are contained in the first 10 columns of rows A and B, then the cell addresses for those arrays are A1:A10 and B1:B10.

Step 3

Click a blank cell that you would like to use to display the covariance between your data arrays.

Step 4

Type the following in the cell:=COVAR(A1:A10,B1:B10)Substitute the cell addresses for your two data arrays in place of the sample addresses in the example.

Step 5

Press "Enter." The cell displays the covariance between the two data arrays.