COVARIANCE.P Function

The COVARIANCE.P function calculates the population covariance between two data sets. It measures the degree to which two variables vary together, providing insight into their linear relationship. The return value from the function is a single number that can be positive, negative, or zero, depending on the relationship between the variables. Unlike correlation, covariance is not standardized and depends on the units of measurement.

Key features

• Calculates population covariance (divides by N, not N-1)
• Both arrays must have the same number of data points
• Returns positive values when variables move together
• Returns negative values when variables move in opposite directions
• Returns values close to zero when variables are independent
• Works with numbers only - text and logical values are ignored

Note: COVARIANCE.P is the newer version of the COVAR function and produces identical results. For sample covariance (divides by N-1), use COVARIANCE.S instead.

Table of contents

• Example #1 - Positive Covariance
• Example #2 - Negative Covariance
• Example #3 - Zero Covariance
• When to use COVARIANCE.P
• COVARIANCE.P vs COVARIANCE.S

Example #1 - Positive Covariance

In this example, we'll examine the relationship between temperature (°F) and ice cream sales ($). As temperature increases, ice cream sales tend to increase as well, demonstrating positive covariance.

=COVARIANCE.P(B5:B9,C5:C9) // returns 335

The positive result indicates that temperature and ice cream sales have a positive relationship. As one increases, the other increases too.

Example #2 - Negative Covariance

Here we examine the relationship between a car's mileage and its resale price. As mileage increases, the car's value typically decreases, showing negative covariance.

=COVARIANCE.P(B5:B9,C5:C9) // returns -160000000

The negative result confirms that mileage and car price have an inverse relationship. As one increases, the other decreases.

Example #3 - Zero Covariance

This example illustrates two variables with no linear relationship: daily temperature and the result of a dice roll. These variables are completely independent of each other.

=COVARIANCE.P(B5:B14,C5:C14) // returns -0.5

The result close to zero indicates no meaningful relationship between temperature and dice roll outcomes.

When to use COVARIANCE.P

Use the COVARIANCE.P function when you need to analyze paired data to understand whether a relationship exists between the two variables. A positive covariance indicates that the two variables tend to move in the same direction. A negative covariance suggests that the variables move in opposite directions. If the result is near zero, it implies there is little to no linear relationship between the two data sets.

The magnitude of the covariance depends on the units of measurement. As a result, it can be challenging to determine the strength of a relationship between two variables from covariance alone. When you care about the magnitude of change between variables, COVARIANCE.P might be the appropriate function for your use case. Otherwise, for a standardized comparison, use the correlation function instead.

COVARIANCE.P vs COVARIANCE.S

Excel provides two covariance functions:

• COVARIANCE.P - Calculates population covariance (divides by N)
• COVARIANCE.S - Calculates sample covariance (divides by N-1)

Use COVARIANCE.P when your data represents the entire population you're interested in. Use COVARIANCE.S when your data is a sample from a larger population and you want to estimate the population covariance.

Formula definition of COVARIANCE.P

The population covariance is calculated using the following formula:

Notes

• Empty cells, text, and logical values are ignored
• Returns #DIV/0! error if arrays are empty after ignoring non-numeric values
• Returns #N/A error if arrays have different lengths
• COVARIANCE.P produces identical results to the older COVAR function
• For sample covariance (divides by N-1), use COVARIANCE.S function