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:

COVARIANCE.P Function 1 - How to use COVARIANCE.P Function in Excel

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

COVARIANCE.P Function - How to use COVARIANCE.P Function in Excel

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: COVARIANCE.P Function in Excel - How to use COVARIANCE.P Function in ExcelwhereCOVARIANCE.P Function in Excel 1 - How to use COVARIANCE.P Function in Excelare the sample means AVERAGE(array1) and AVERAGE(array2), and n is the sample size.

READ:  How to use NORM.INV Function in Excel

Add a Comment

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