COVARIANCE.S Function (LibreOffice Calc)
The COVARIANCE.S function in LibreOffice Calc returns the sample 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.S Function Does â–¾
- Calculates sample covariance
- Measures how two variables move together
- Uses the ( n - 1 ) denominator (sample correction)
- Works with numeric X/Y pairs
- Useful for inferential statistics, regression, and modeling
- Works across sheets
COVARIANCE.S is the correct function when your data is a sample.
Syntax â–¾
COVARIANCE.S(array1; array2)
Where:
array1— first dataset (X values)array2— second dataset (Y values)
Both arrays must be the same size.
Interpretation of Sample 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 â–¾
Sample covariance between two datasets
=COVARIANCE.S(A1:A10; B1:B10)
Covariance across sheets
=COVARIANCE.S(Sheet1.A1:A50; Sheet2.B1:B50)
Covariance using named ranges
=COVARIANCE.S(Height; Weight)
Covariance with dates as X-values
=COVARIANCE.S(A1:A100; B1:B100)
(Calc converts dates to serial numbers.)
Advanced Examples â–¾
Covariance ignoring errors
=COVARIANCE.S(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.S.
Covariance after removing outliers
=COVARIANCE.S(FILTER(A1:A100; A1:A100<1000); FILTER(B1:B100; A1:A100<1000))
Covariance for time-series analysis
=COVARIANCE.S(Sales; MarketingSpend)
Covariance for normalized data
=COVARIANCE.S((A1:A10 - AVERAGE(A1:A10)); (B1:B10 - AVERAGE(B1:B10)))
Covariance for log-transformed data
=COVARIANCE.S(LN(A1:A10); LN(B1:B10))
How COVARIANCE.S Calculates Sample Covariance â–¾
The formula is:
[ \text{cov}_S(x, y) = \frac{\sum (x_i - \bar{x})(y_i - \bar{y})}{n - 1} ]
Where:
- ( \bar{x} ) = mean of X
- ( \bar{y} ) = mean of Y
- ( n ) = number of data points
This is the sample version.
For population covariance (divide by ( n )), use COVARIANCE.P.
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.S 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.S differs from COVARIANCE.P
- COVARIANCE.S divides by ( n - 1 )
- COVARIANCE.P divides by ( n )
Best Practices â–¾
- Use COVARIANCE.S when your data is a sample
- Use COVARIANCE.P for full-population datasets
- 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.S is essential for inferential statistics — whenever your dataset is a sample, this is the correct covariance function to use.