The Excel COVARIANCE.P Function
Related Function:
COVARIANCE.S
Covariance
The covariance is a statistical measurement of the strength of the correlation between two sets of variables, and is calculated by the following equation:
where x and y are the sample means (averages) of the two sets of values, and n is the sample size.
For further information, see the Wikipedia Covariance page
Function Description
The Excel COVARIANCE.P function calculates the population covariance of two supplied sets of values.
The function is new in Excel 2010 and so is not available in earlier versions of Excel. However, the Covariance.P function is simply an updated version of the Covar function, which is available in earlier versions of Excel.
The syntax of the Covariance.P function is:
COVARIANCE.P( array1, array2 )
Where array1 and array2 are two arrays of numeric values, that are of equal length.
Note that if the supplied arrays contain text or logical values, these are ignored by the Covariance.P function.
Covariance.P Function Example
| A | B | |
|---|---|---|
| 1 | array1 | array2 |
| 2 | 2 | 22.90 |
| 3 | 7 | 33.49 |
| 4 | 8 | 34.50 |
| 5 | 3 | 27.61 |
| 6 | 4 | 19.5 |
| 7 | 1 | 10.11 |
| 8 | 6 | 37.90 |
| 9 | 5 | 31.08 |
Columns A and B of the above spreadsheet on the right contain two sets of values.
The population covariance of the values in columns A and B of the spreadsheet can be calculated using the Excel Covariance.P function, as follows:
=COVARIANCE.P( A2:A9, B2:B9 )
This gives the result 16.633125, which indicates a positive correlation between the two sets of values.
See the Microsoft Office website for further information and examples of the Excel Covariance.P function.
Covariance.P Function Errors
If you get an error from the Excel Covariance.P Function, this is likely to be one of the following:
Common Errors
| #N/A | - | Occurs if the two supplied arrays have different lengths. |
| #VALUE! | - | Occurs if one or both of the supplied arrays are empty. |