How Do You Calculate Covariance in Excel?
To calculate covariance in Excel, use the covariance function. The syntax of the function is: COVARIANCE.P(array1,array2), where array1 and array2 are the two sets of data for which the covariance is being determined.
-
Arrange the two sets of data in array format
To arrange in array format means to arrange in either a column or row format.
-
Enter the formula for variance in a separate cell
To use formulas in Excel, precede the formula with the equal sign. Thus, to determine covariance, enter “=COVARIANCE.P(array1,array2).” Without the equal sign, Excel will not calculate covariance because the formula will be recognized only as text.
-
Replace the variables array1 and array2 in the covariance formula with real data
To enter the data for array1, highlight the first cell of the column. A small black square appears at the bottom right corner of the highlighted cell. Place the cursor on it. When the cursor turns into a small black cross, drag down to select the range down to the last cell. Do the same for array2. Press enter; the covariance figure appears in the cell in the place of the formula.
