Practice free

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.

Practice this in the app

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.

FormulaExcel showsCase
=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.

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 .