Excel functions / Statistics
COVARIANCE.S function
Sample covariance: like COVARIANCE.P, but divides by n − 1 for a sample.
Syntax
=COVARIANCE.S(array1, array2)
Arguments
array1: The first set of values.array2: The second set, the same size as the first.
When to use it
COVARIANCE.S measures how two sets of numbers move together in a sample: positive when they rise together, negative when one falls as the other rises. Its size depends on the units, so CORREL is easier to read; covariance is what goes into portfolio risk and regression.
Examples
Ad spend against sales
A B C D 1 Ad spend Sales 2 100 1200 247500 3 200 1900 4 300 3100 5 400 3800 6 500 5200 In D2:
=COVARIANCE.S(A2:A6, B2:B6)→ 247500
Common mistakes
- Reading the size. A covariance of 247,500 is not "large" by itself: it is in dollars times dollars. Use CORREL to compare strength.
COVARIANCE.S in other languages
Excel shows function names in its own language. In these languages it also puts a semicolon between arguments where English has a comma.
- Czech: COVARIANCE.S
- Danish: KOVARIANS.S
- Dutch: COVARIANTIE.S
- Finnish: KOVARIANSSI.S
- French: COVARIANCE.STANDARD
- German: KOVARIANZ.S
- Hungarian: KOVARIANCIA.M
- Italian: COVARIANZA.C
- Norwegian: KOVARIANS.S
- Polish: KOWARIANCJA.PRÓBKI
- Portuguese (Brazil): COVARIAÇÃO.S
- Portuguese (Portugal): COVARIÂNCIA.S
- Russian: КОВАРИАЦИЯ.В
- Spanish: COVARIANZA.M
- Swedish: KOVARIANS.S
- Turkish: KOVARYANS.S
Try COVARIANCE.S in the playground
Related functions
- COVARIANCE.P: Population covariance: the average product of the deviations of each pair from their means.
- CORREL: The correlation coefficient of two data sets: from −1 (opposite) through 0 (unrelated) to 1 (move together).
- VAR: Variance of a sample.
- SLOPE: The slope of the straight line that best fits the data: how much y changes for each 1 added to x.