COVAR Function (LibreOffice Calc)
The COVAR function in LibreOffice Calc returns the 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 COVAR Function Does â–¾
- Calculates covariance between two datasets
- Measures how two variables move together
- Works with numeric X/Y pairs
- Useful for correlation, regression, and exploratory analysis
- Works across sheets
COVAR is the legacy version of covariance.
Modern spreadsheets use COVARIANCE.P and COVARIANCE.S.
Syntax â–¾
COVAR(array1; array2)
Where:
array1— first dataset (X values)array2— second dataset (Y values)
Both arrays must be the same size.
Interpretation of 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 â–¾
Covariance between two datasets
=COVAR(A1:A10; B1:B10)
Covariance across sheets
=COVAR(Sheet1.A1:A50; Sheet2.B1:B50)
Covariance using named ranges
=COVAR(Height; Weight)
Covariance with dates as X-values
=COVAR(A1:A100; B1:B100)
(Calc converts dates to serial numbers.)
Advanced Examples â–¾
Covariance ignoring errors
=COVAR(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 COVAR.
Covariance after removing outliers
=COVAR(FILTER(A1:A100; A1:A100<1000); FILTER(B1:B100; A1:A100<1000))
Covariance for time-series analysis
=COVAR(Sales; MarketingSpend)
Covariance for normalized data
=COVAR((A1:A10 - AVERAGE(A1:A10)); (B1:B10 - AVERAGE(B1:B10)))
Covariance for log-transformed data
=COVAR(LN(A1:A10); LN(B1:B10))
How COVAR Calculates Covariance â–¾
The formula is:
[ \text{cov}(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 population covariance.
Sample covariance divides by ( n - 1 ) instead — use COVARIANCE.S for that.
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
COVAR 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
COVAR differs from COVARIANCE.P / COVARIANCE.S
- COVAR = population covariance (legacy)
- COVARIANCE.P = population covariance (modern)
- COVARIANCE.S = sample covariance
Best Practices â–¾
- Use COVAR for compatibility with older spreadsheets
- Prefer COVARIANCE.P or COVARIANCE.S for modern work
- 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 is the raw measure of how two variables move together — the foundation of correlation, regression, and predictive modeling.