COVARIANCE.S Function

Returns the sample covariance of two sets of numbers.

Category

Statistical

Available in

Excel 2010+, Microsoft 365

Returns

A number

Want to practice COVARIANCE.S in a real spreadsheet? Open a blank sheet in our Practice Sandbox and try it with live data, with the AI Excel tutor beside you. No Excel needed — just a free account.Try it free in the Sandbox

Overview

Covariance shows whether two sets of numbers move in the same direction: positive when they rise together, negative when one rises as the other falls, near 0 when there's no pattern.

Its size depends on the units (here, $k × $k), so it's hard to read on its own — CORREL rescales it to −1…1. Covariance is used inside formulas for regression and portfolio risk.

COVARIANCE.S treats the data as a sample and divides by n − 1. Use COVARIANCE.P when the pairs are the entire population.

Syntax

=COVARIANCE.S(array1, array2) Explain

Arguments

ArgumentRequired?What it does
array1YesThe first range of numbers.
array2YesThe second range, the same size as array1.

Examples

Example 1 — Ad spend and sales

Eight months treated as a sample of Northstar's advertising results.

E2fx=COVARIANCE.S(B2:B9,C2:C9)
ABCDE
1MonthAd spend ($k)Sales ($k)MeasureResult
2Jan221Sample16.43
3Feb325Population14.375
4Mar533
5Apr428
6May638
7Jun740
8Jul846
9Aug531
=COVARIANCE.S(B2:B9,C2:C9) Explain

Positive: higher spend goes with higher sales.

Example 2 — Slope from covariance

A regression slope is the covariance divided by the variance of x.

=COVARIANCE.S(B2:B9,C2:C9)/VAR.S(B2:B9) Explain

Returns about 4.107 — the same as SLOPE(C2:C9,B2:B9).

Common mistakes

  • #DIV/0! There are fewer than two pairs of numbers.
  • #N/A The ranges are different sizes.

Notes

Kyle Stott

Author

Kyle Stott

Certified Microsoft Excel Expert

Kyle has worked professionally with Microsoft Excel for over a decade and has consulted on and taught Excel best practices to over 400 companies across 30+ countries.