by 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:
- Array1 (required argument) – This is a range or array of integer values.
- 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:
where
are the sample means AVERAGE(array1) and AVERAGE(array2), and n is the sample size.
