COVARIANCE.P Function (LibreOffice Calc)
The COVARIANCE.P function in LibreOffice Calc returns the population covariance between two datasets. This guide explains syntax, interpretation, examples, errors, and best practices.
Compatibility
▾| Excel | ✔ |
| Gnumeric | ✔ |
| Google_sheets | ✔ |
| Libreoffice | ✔ |
| Numbers | ✖ |
| Onlyoffice | ✔ |
| Openoffice | ✖ |
| Wps | ✔ |
| Zoho | ✔ |
What the COVARIANCE.P Function Does ▾
- Calculates population covariance
- Measures how two variables move together
- Works with numeric X/Y pairs
- Useful for correlation, regression, and statistical modeling
- Works across sheets
COVARIANCE.P is the correct function when your data represents the entire population.
Syntax ▾
COVARIANCE.P(array1; array2)
Where:
array1— first dataset (X values)array2— second dataset (Y values)
Both arrays must be the same size.
Interpretation of Population Covariance ▾
| Covariance | Meaning |
|---|---|
| > 0 | Variables tend to increase together |
| < 0 | One increases while the other decreases |
| = 0 | No linear relationship (but non-linear possible) |
Covariance is not normalized, so its magnitude depends on the scale of the data.
Basic Examples ▾
Population covariance between two datasets
=COVARIANCE.P(A1:A10; B1:B10)
Covariance across sheets
=COVARIANCE.P(Sheet1.A1:A50; Sheet2.B1:B50)
Covariance using named ranges
=COVARIANCE.P(Height; Weight)
Covariance with dates as X-values
=COVARIANCE.P(A1:A100; B1:B100)
(Calc converts dates to serial numbers.)
Advanced Examples ▾
Covariance ignoring errors
=COVARIANCE.P(IF(ISNUMBER(A1:A100); A1:A100); IF(ISNUMBER(B1:B100); B1:B100))
(Confirm with Ctrl+Shift+Enter in older Calc.)
Covariance using filtered (visible) data only
Use SUBTOTAL helper column to filter X/Y before passing to COVARIANCE.P.
Covariance after removing outliers
=COVARIANCE.P(FILTER(A1:A100; A1:A100<1000); FILTER(B1:B100; A1:A100<1000))
Covariance for time-series analysis
=COVARIANCE.P(Sales; MarketingSpend)
Covariance for normalized data
=COVARIANCE.P((A1:A10 - AVERAGE(A1:A10)); (B1:B10 - AVERAGE(B1:B10)))
Covariance for log-transformed data
=COVARIANCE.P(LN(A1:A10); LN(B1:B10))
How COVARIANCE.P Calculates Population Covariance ▾
The formula is:
[ \text{cov}_P(x, y) = \frac{\sum (x_i - \bar{x})(y_i - \bar{y})}{n} ]
Where:
- ( \bar{x} ) = mean of X
- ( \bar{y} ) = mean of Y
- ( n ) = number of data points
This is the population version.
For sample covariance (divide by ( n - 1 )), use COVARIANCE.S.
Common Errors and Fixes ▾
Err:502 — Invalid argument
Occurs when:
- Arrays have different sizes
- One or both arrays contain no numeric values
- Arrays contain only one data point
Err:504 — Parameter error
Occurs when:
- Semicolons are incorrect
- Range references malformed
COVARIANCE.P returns unexpected value
Possible causes:
- Relationship is non-linear
- Outliers distort covariance
- X-values or Y-values contain hidden text
- Data contains zeros that should be excluded
COVARIANCE.P differs from COVAR
- COVARIANCE.P = modern population covariance
- COVAR = legacy population covariance
Best Practices ▾
- Use COVARIANCE.P when your data represents the entire population
- Use COVARIANCE.S for sample-based statistics
- Use CORREL when you need normalized covariance
- Remove outliers when appropriate
- Use named ranges for cleaner formulas
- Use LINEST when you need full regression diagnostics
COVARIANCE.P is the modern, standards‑aligned way to measure how two variables move together across an entire population — essential for regression, modeling, and exploratory analytics.