The Excel COVAR Function
Related Function:
VAR Function
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
COVAR and COVARIANCE.P
In Excel 2010, the COVAR function has been replaced by the COVARIANCE.P function, which has improved accuracy.
Although it has been replaced, the Covar function is still available in Excel 2010 (stored in the list of compatibility functions), to allow compatibility with earlier versions of Excel.
Function Description
The Excel COVAR function calculates the covariance of two supplied sets of values.
The syntax of the function is:
COVAR( array1, array2 )
Where array1 and array2 are two arrays of numeric values.
Note that if the supplied arrays contain text or logical values, these are ignored by the Covar function.
Covar 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 Covariance of the values in columns A and B of the spreadsheet can be calculated using the Excel Covar function, as follows:
=COVAR( A2:A9, B2:B9 )
This gives the result 16.633125, which indicates a positive correlation between the two sets of values.
For further examples of the Excel Covar function, see the Microsoft Office website.
Covar Function Errors
If you get an error from the Excel Covar 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 data arrays are empty. |