COVARIANCE.P Function in Excel

Part 1: Introduction

The COVARIANCE.P function in Excel determines the covariance, a measure of how much two random variables vary together. It’s often used in probability theory and statistics, particularly finance and investment.

The syntax for the COVARIANCE.P function is as follows:

=COVARIANCE.P(array1, array2)

The arguments are:

• array1: The first cell range of integers.
• array2: The second cell range of integers.

This function will return a numerical value representing the two arrays’ covariance. A positive value indicates that the arrays increase and decrease together (positive correlation). In contrast, a negative value indicates that one collection tends to increase when the other decreases (negative correlation).

Note: The COVARIANCE.P function calculates the “population” covariance. For a “sample” covariance, use COVARIANCE.S.

Part 2: Examples

Example 1:

Purpose: To determine the covariance of the quarterly sales of two products, A and B.

Data tables and formulas:

Explanation: The positive covariance value suggests that the sales of Product A and Product B tend to increase and decrease together across quarters.

Example 2:

Purpose: To determine the covariance of the monthly production units of two factories.

Data tables and formulas:

Explanation: The positive covariance value suggests that the monthly production units of Factory 1 and Factory 2 tend to increase and decrease together.

Example 3:

Purpose: To determine the covariance of the daily sales of two different restaurant branches.

Data tables and formulas:

Explanation: The positive covariance value suggests that the daily sales of Branches 1 and 2 tend to increase and decrease together.

Example 4:

Purpose: To determine the covariance of the monthly customers of two services, Service X and Service Y.

Data tables and formulas:

Explanation: The positive covariance value suggests that Service X and Service Y’s monthly customers tend to increase and decrease together.

Example 5:

Purpose: To determine the covariance of the monthly sales of two products, Product C and Product D.

Data tables and formulas:

Explanation: The negative covariance value suggests that the monthly sales of Product C and Product D tend to move in opposite directions.

Part 3: Tips and Tricks

1. Covariance is a measure of correlation. But unlike the correlation coefficient, covariance is measured in units that are the product of the branches of the two variables. Therefore, it cannot be easy to interpret.
2. A positive covariance means the variables are positively related, while a negative covariance means the variables are inversely related.
3. Covariance is sensitive to changes in the center or scale of the variables. For a standardized measure of correlation, use the CORREL function to find the correlation coefficient.
4. Be cautious when interpreting the results. Covariance alone doesn’t explain the dependency relationship between the two variables. It only suggests the direction of the relationship.
5. Always double-check your ranges – array1 and array2 must have the same number of data points.
6. Covariance can be helpful in financial analysis, for example, to understand how different securities in a portfolio move about one another.