How to Calculate Sample Covariance with COVARIANCE.S

COVARIANCE.S shows whether two measures move together in a sample — and is the building block of correlation.

You will learn to

• Calculate a sample covariance
• Recognise a negative covariance
• Convert covariance into a correlation

How it works

COVARIANCE.S(array1, array2) multiplies each pair’s deviations from their means, adds them up and divides by n − 1. A positive result means the two tend to be above average together; negative means when one is high the other tends to be low.

The size of a covariance depends on the units, so 1,500 isn’t “big” or “small” on its own. Divide by both standard deviations — COVARIANCE.S/(STDEV.S×STDEV.S) — and you get the correlation coefficient, which runs from −1 to +1. Use COVARIANCE.S for samples and COVARIANCE.P for complete populations.

=COVARIANCE.S(B2:B11,C2:C11)