What is the difference between COVARIANCE.P and COVARIANCE.S in Excel?

---

In statistics, covariance is a way to measure how changes in one variable are associated with changes in another variable.

A positive value for covariance indicates that an increase in one variable is associated with an increase in another variable.

A negative value indicates that an increase in one variable is associated with a decrease in another variable.

There are two different functions you can use to calculate covariance in Excel:

1. COVARIANCE.P: This function calculates the population covariance. Use this function when the range of values represents the entire population.

This function uses the following formula:

Population covariance = Σ(x_i–x)(y_i–y) / n

where:

• Σ: A greek symbol that means “sum”
• x_i: The i^th value for variable x
• x: The mean value for variable x
• y_i: The i^th value for variable y
• y: The mean value for variable y
• n: The total number of observations

2. COVARIANCE.S: This function calculates the sample covariance. Use this function when the range of values represents a sample of values, rather than an entire population.

This function uses the following formula:

Sample covariance = Σ(x_i–x)(y_i–y) / (n-1)

where:

• Σ: A greek symbol that means “sum”
• x_i: The i^th value for variable x
• x: The mean value for variable x
• y_i: The i^th value for variable y
• y: The mean value for variable y
• n: The total number of observations

Notice the subtle difference between the two formulas: COVARIANCE.P divides by n while COVARIANCE.S divides by n-1.

Because of this, the COVARIANCE.S formula will always produce a larger value because it divides by a smaller value.

The following example shows how to use each formula in practice.

Example: COVARIANCE.P vs. COVARIANCE.S in Excel

Suppose we have the following dataset in Excel that shows the points and assists for 15 different basketball players:

The following screenshot shows how to calculate the covariance between Points and Assists using the two different covariance formulas:

The sample covariance turns out to be 15.69 and the population covariance turns out to be 14.64.

As mentioned earlier, the sample covariance will always be larger than the population covariance.

When to Use COVARIANCE.P vs. COVARIANCE.S

In most cases, we’re unable to collect data for an entire population so we instead collect data for just a of the population.

Thus, we almost always use COVARIANCE.S to calculate the covariance of a dataset because our dataset typically represents a sample.

In the rare case where your data represents an entire population, you may use the COVARIANCE.P function instead.

The following tutorials explain the difference between other commonly used Excel functions:

QUARTILE.EXC vs. QUARTILE.INC in Excel: What’s the Difference?