Excel like a finance pro.
Statistical function
COVARIANCE.S
Returns the sample covariance of two data sets.
When to use it
Sample covariance: how two variables move together, in the product of their units. Positive when they rise together. Divide by the two standard deviations to get the correlation.
The shape of it
- Syntax
=COVARIANCE.S(array1, array2)
Worked examples
The shared dataset
=COVARIANCE.S({1,2,3,4},{2,4,6,9}) → 3.833333333
Sum of products of deviations (11.5) over n - 1.
Two return series
=COVARIANCE.S(A2:A253,B2:B253) → 0.000182
Two stocks' daily returns.
Back to correlation
=COVARIANCE.S(A2:A13,B2:B13)/(STDEV.S(A2:A13)*STDEV.S(B2:B13)) → 0.83
Equals CORREL.
Worth knowing
- Portfolio variance needs the full covariance matrix; build it with COVARIANCE.S for each pair.
- Beta of a stock: COVARIANCE.S(stock,market)/VAR.S(market).
- COVARIANCE.P divides by n.
Where it goes wrong
- #N/A when the arrays differ in size.
- Units are hard to interpret; use CORREL for a scale-free number.
Related
Learn the moves here — or let Wauvel run them on your numbers.
Meet your AI CFO →One CFO-grade Excel tip a week
A short, practical email for finance operators — functions, shortcuts, and the moves that save an afternoon. Free, unsubscribe anytime.