Browse

COVARIANCE.S Function (LibreOffice Calc)

Math Intermediate LibreOffice Calc LibreOffice 4.0

The COVARIANCE.S function in LibreOffice Calc returns the sample covariance between two datasets. This guide explains syntax, interpretation, examples, errors, and best practices.

The **COVARIANCE.S** function returns the **sample covariance** between two datasets. It measures how two variables move together when your data represents a **sample** rather than an entire population.

This guide provides syntax, interpretation rules, examples, common errors, and best practices, making it one of the most complete COVARIANCE.S references available for LibreOffice Calc.

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.