How to Calculate Covariance in Excel

Covariance is a statistical measure that shows how two variables change together. In simpler terms, it tells you whether two datasets tend to move in the same direction, in opposite directions, or if they have no clear relationship. Understanding covariance is crucial in fields like finance, economics, and data analysis for assessing relationships between different factors.

What is Covariance?

Covariance measures the directional relationship between the returns on two assets. A positive covariance indicates that the asset returns move together, while a negative covariance indicates they move inversely. A covariance of zero suggests no linear relationship between the two variables.

• Positive Covariance: When one variable increases, the other variable tends to increase as well. When one decreases, the other tends to decrease.
• Negative Covariance: When one variable increases, the other variable tends to decrease, and vice versa.
• Zero Covariance: There is no consistent linear relationship between the movements of the two variables.

It's important to note that covariance does not measure the strength of the relationship, only its direction. For strength, you'd typically look at correlation, which is a standardized version of covariance.

The Covariance Formula

There are two main types of covariance: population covariance and sample covariance.

Population Covariance (σxy)

Used when you have data for the entire population:

σxy = Σ[(Xi - μx)(Yi - μy)] / N

Where:

• Xi = individual data point for variable X
• Yi = individual data point for variable Y
• μx = mean of variable X
• μy = mean of variable Y
• N = total number of data points in the population

Sample Covariance (Sxy)

Used when you have data for a sample taken from a larger population:

Sxy = Σ[(Xi - X̄)(Yi - Ȳ)] / (n - 1)

Where:

• Xi = individual data point for variable X
• Yi = individual data point for variable Y
• X̄ = sample mean of variable X
• Ȳ = sample mean of variable Y
• n = total number of data points in the sample

The (n - 1) in the denominator for sample covariance is known as Bessel's correction, which helps to provide an unbiased estimate of the population covariance.

Calculating Covariance in Excel Using Functions

Excel provides built-in functions to easily calculate both population and sample covariance.

1. COVARIANCE.P (Population Covariance)

This function calculates the population covariance of two datasets.

Syntax: COVARIANCE.P(array1, array2)

Example:

1. Open a new Excel worksheet.
2. Enter your first dataset in cells A1:A5 (e.g., 10, 12, 15, 18, 20).
3. Enter your second dataset in cells B1:B5 (e.g., 5, 6, 7, 8, 9).
4. In an empty cell (e.g., C1), type: =COVARIANCE.P(A1:A5, B1:B5)
5. Press Enter.

For the example data (A: 10,12,15,18,20; B: 5,6,7,8,9), the COVARIANCE.P result would be 5.2.

2. COVARIANCE.S (Sample Covariance)

This function calculates the sample covariance of two datasets. This is often the more commonly used function when working with sample data.

Syntax: COVARIANCE.S(array1, array2)

Example:

1. Using the same datasets as above (A1:A5 and B1:B5).
2. In an empty cell (e.g., C2), type: =COVARIANCE.S(A1:A5, B1:B5)
3. Press Enter.

For the example data (A: 10,12,15,18,20; B: 5,6,7,8,9), the COVARIANCE.S result would be 6.5.

Manual Calculation of Covariance in Excel

While functions are convenient, understanding the manual steps helps in grasping the concept.

Let's use the datasets:

• Dataset X: 10, 12, 15, 18, 20
• Dataset Y: 5, 6, 7, 8, 9

Step-by-Step Guide:

1. Enter Data:
   • In Column A (X), enter: 10, 12, 15, 18, 20
   • In Column B (Y), enter: 5, 6, 7, 8, 9
2. Calculate Means:
   • In cell A7, type =AVERAGE(A1:A5) (Result: 15)
   • In cell B7, type =AVERAGE(B1:B5) (Result: 7)
3. Calculate Deviations from the Mean (X - X̄):
   • In cell C1, type =A1-$A$7 and drag down to C5.
   • Results for Column C: -5, -3, 0, 3, 5
4. Calculate Deviations from the Mean (Y - Ȳ):
   • In cell D1, type =B1-$B$7 and drag down to D5.
   • Results for Column D: -2, -1, 0, 1, 2
5. Calculate the Product of Deviations:
   • In cell E1, type =C1*D1 and drag down to E5.
   • Results for Column E: 10, 3, 0, 3, 10
6. Sum the Products of Deviations:
   • In cell E7, type =SUM(E1:E5) (Result: 26)
7. Calculate Covariance:
   • For Population Covariance: Divide the sum by N (number of data points).
     • In cell F1, type =E7/COUNT(A1:A5) (Result: 5.2)
   • For Sample Covariance: Divide the sum by (N-1).
     • In cell F2, type =E7/(COUNT(A1:A5)-1) (Result: 6.5)

Interpreting Covariance Results

The sign of the covariance (positive or negative) is more important than its magnitude. A large positive or negative covariance value doesn't necessarily mean a strong relationship, as covariance is not standardized. Its value depends on the units of the variables.

• Positive Covariance: Indicates a direct relationship. As one variable increases, the other tends to increase.
• Negative Covariance: Indicates an inverse relationship. As one variable increases, the other tends to decrease.
• Near Zero Covariance: Suggests little to no linear relationship.

To understand the strength of the relationship, you should calculate the correlation coefficient, which standardizes covariance by dividing it by the product of the standard deviations of the two variables.

Practical Applications of Covariance

• Finance: Used in portfolio theory to measure how the returns of two different assets move in relation to each other. This helps in diversification strategies.
• Economics: Analyzing the relationship between economic indicators, such as inflation and unemployment.
• Data Science: Feature engineering and understanding relationships between variables in a dataset before building predictive models.
• Biology: Studying how the growth of different species might be related to environmental factors.

Conclusion

Covariance is a fundamental statistical tool for understanding the directional relationship between two variables. Excel makes it easy to calculate both population and sample covariance using its built-in functions, COVARIANCE.P and COVARIANCE.S. While its magnitude can be hard to interpret directly, its sign provides valuable insights into how variables move together, laying the groundwork for more advanced statistical analyses like correlation and regression.