How to use COVARIANCE.P Function in Excel

COVARIANCE.P function in excel is a statistical function. This function is used to find the variability of two data sets which are co-variant to each other. For example if we needs to find whether greater levels of inflation leads to greater levels of inflation, than we can use excel function. This is a worksheet function and can be entered into the cell in which count is required.

Syntax:= COVARIANCE.P(array1,array2)

The COVARIANCE.P function syntax has the following arguments:

  1. Array1 (required argument) – This is a range or array of integer values.
  2. Array2 (required argument) – This is a second range or array of integer values.

Example: Let’s look at some Excel COVARIANCE.P function examples and explore how to use the COVARIANCE.P function as a worksheet function in Microsoft Excel:

Suppose we are given the monthly returns of two assets, gold and bitcoin, as shown below:

We wish to find out covariance in Excel, that is, to determine if there is any relation between the two. The relationship between the values in columns B and C can be calculated using the formula:

Syntax:  =COVARIANCE.P(B2:B13,C2:C13)

Result: 0.0125

The formula gives the result 0.0125, which indicates a negative correlation between the two assets.

Note:

  • The arguments must either be numbers or be names, arrays, or references that contain numbers.
  • If an array or reference argument contains text, logical values, or empty cells, those values are ignored; however, cells with the value zero are included.
  • If array1 and array2 have different numbers of data points, COVARIANCE.P returns the #N/A error value.
  • If either array1 or array2 is empty, COVARIANCE.P returns the #DIV/0! error value.
  • The covariance is: whereare the sample means AVERAGE(array1) and AVERAGE(array2), and n is the sample size.

Add a Comment

Your email address will not be published. Required fields are marked *