COVARIANCE.P function
COVARIANCE.P shows whether two series tend to move in the same direction, averaging the product of their deviations. Use it when you have the full population rather than a sample.
Example
Read it as a sentence: find how the values in G1:G5 and F1:F5 vary together, treating them as the whole population.
Excel shows 1.2
F
G
1
1
2
2
2
4
3
3
5
4
4
4
5
5
5
Data
Syntax
COVARIANCE.P(array1, array2)
-
array1(required) - First set of values.
-
array2(required) - Second set, same size.
Returns: The population covariance of two data sets.
What Excel actually returns
Each formula below was run in desktop Excel. The result is exactly what the cell showed, errors included.
| Formula | Excel shows | Case |
|---|---|---|
=COVARIANCE.P({2,4,5,4,5},{1,2,3,4,5}) | 1.2 | Array constants |
=COVARIANCE.P(Data!G1:G5,Data!F1:F5) | 1.2 | Two ranges (y,x order as written) |
=COVARIANCE.P(Data!I1:I5,Data!H1:H5) | 2 | Blank and text-number pairs dropped |
=COVARIANCE.P(Data!G1:G5,Data!J1:J5) | 0 | Constant x |
=COVARIANCE.P(Data!G1,Data!F1) | 0 | Single pair |
=COVARIANCE.P(Data!G1:G2,Data!F1:F2) | 0.5 | Two pairs |
=COVARIANCE.P(Data!G1:G5,Data!K1:K3) | #N/A | Different sizes |
=COVARIANCE.P(Data!C1:C5,Data!F1:F5) | #N/A | Range containing #N/A |
The Data sheet these formulas read
A cell that starts with = holds that formula.
C
F
G
H
I
J
K
1
1
1
2
1
2
3
1
2
2
2
4
4
3
2
3
=NA()
3
5
3
5
3
3
4
4
4
4
4
4
3
5
5
5
5
5
5
3
Data
Watch out for
- Units are the product of both series, so it is hard to read; CORREL is easier to interpret.
- Divides by n; COVARIANCE.S divides by n-1.
Where it works
- Excel
- COVARIANCE.P arrived in Excel 2010. Older versions do not have it.
- Google Sheets
- Same name, same behaviour for everyday use.
Part of Statistical functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .