The Excel COVARIANCE.S function returns the sample covariance — the average of the products of paired deviations — measuring how two variables vary together across a sample drawn from a larger population.
Syntax
| Argument | Description | |
|---|---|---|
array1 | Required | The first range or array of values. |
array2 | Required | The second range or array, with the same number of values as array1. Each value is paired with the value in the same position of array1. |
How to use it
COVARIANCE.S multiplies each pair's deviations from their means, sums the products, then divides by n−1 (the sample correction). A positive result means the two variables tend to rise together; a negative result means one rises as the other falls.
The only difference from COVARIANCE.P is the divisor: the .S version divides by n−1 (use it when your data is a sample), while .P divides by n (use it when the data is the entire population). For the same data, COVARIANCE.S is always the larger of the two.
Both arrays must contain the same number of numeric values. Text, logical values, and empty cells in a paired position are ignored.
Tip: Covariance is scale-dependent, so its raw size is hard to interpret. To get a unitless −1 to +1 measure of how tightly the variables move together, use CORREL instead.
Try it: interactive demo
Enter two comma-separated lists of equal length to see the sample covariance.
Practice workbook
Frequently asked questions
What is the difference between COVARIANCE.S and COVARIANCE.P?
n−1 and is for sample data; COVARIANCE.P divides by n and is for an entire population. For the same numbers, the .S result is always slightly larger.Should I use covariance or correlation?
CORREL normalizes covariance to a −1 to +1 scale, so it is far easier to interpret the strength of the relationship.Do the two arrays have to be the same size?
array1 and array2 hold different numbers of data points, COVARIANCE.S returns the #N/A error. Each value is paired by position.Is COVARIANCE.S available in older Excel versions?
COVAR function exists, and it computes the population covariance (the equivalent of COVARIANCE.P).Master functions like this in one day
This page covers one function. Our Excel Formulas and Functions class covers the 30 that matter most — live, hands-on, taught by professionals in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class