The Excel COVARIANCE.S function calculates the sample covariance of two supplied sets of values.
The function is new in Excel 2010 and so is not available in earlier versions of Excel.
The syntax of the Covariance.S function is:
Where array1 and array2 are two arrays of numeric values, which are of equal length.
Note that if the supplied arrays contain text or logical values, these are ignored by the Covariance.S function.
| 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 sample covariance of the values in columns A and B of the spreadsheet can be calculated using the Excel Covariance.S function, as follows:
This gives the result 19.00928571, which indicates a positive correlation between the two sets of values.
For further examples of the Excel Covariance.S function, see the Microsoft Office website.
If you get an error from the Excel Covariance.S Function, this is likely to be one of the following:
| #N/A | - | Occurs if the supplied array1 and array2 are of different lengths. |
| #VALUE! | - | Occurs if one or both of the supplied arrays are empty. |