As you know Excel lets you perform all kinds of tasks and it has a huge variety of features that directly deals with statistical functions. The Covariance is one of these functions that helps in calculating the mutual variability of two random variables in the two datasets.
Suppose, you need to figure out whether greater income holds a greater education level in a sample or not, and in this case Covariance. P helps you. This function was first used in 2010 that simply replaces COVAR. In this post, you will get to know how to calculate Covariance in Excel with the help of simple methods.
Covariance Syntax in Excel:
=COVARIANCE.P(array1, array2)
Below is the explanation of arguments used in the COVARIANCE.P:
Array1: It is a compulsory argument which is a range of integer values.
Array2: It is also a required argument that is used as a second range of integer values.
Before getting into the procedure of calculating Covariance, let’s have a look at some vital points you need to consider about these arguments:
- Make sure the datasets are not blank and the standard deviation of these values is not equal to zero.
- Also, make sure the size of the datasets must be equal to the same data points.
- COVARIANCE in the Excel function will simply ignore the text or logical values in the given arrays.
- Your data must consist of names, numbers, references, or arrays that are numeric. If a cell is not having numeric data, it will simply get ignored.
How to Calculate Covariance in Excel?
Coming back to the main topic, let’s see how you can use this function with the help of examples:
Covariance Excel – Example 1
Let’s assume that you are getting monthly profits from two assets gold and bitcoin:
Here you need to find out covariance in Excel. Below is the formula that helps in calculating the relation between the values of Columns C and D:
=COVARIANCE.P(C5:C16,D5:D16)
If you have ever used the covariance function, you must be familiar with the fact that it is a statistical dimension of the variability of the correlation between two sets of variables.
Here,
x and y are the sample means of the two given sets
n is the size of the sample
In this example, the correlation is not positive because the result value is negative.
How to Calculate Covariance in Excel with VBA Code?
VBA lets you calculate Covariance in Excel with the COVAR function. COVAR is a built-in function in Excel that helps in finding covariance between two sets.
Below you can see an example of how COVAR in VBA works:
Sub CalculateCovariance()
Dim xValues As Range, yValues As Range
Dim covariance As Double
' Set the range of cells containing the X values
Set xValues = Range("A2:A6")
' Set the range of cells containing the Y values
Set yValues = Range("B2:B6")
' Calculate the covariance using the COVAR function
covariance = Application.WorksheetFunction.Covar(xValues, yValues)
' Output the result
Range("C2").Value = "Covariance:"
Range("D2").Value = covariance
End Sub
This example shows two cell ranges for the X values and Y values. While using the COVAR function, two cell ranges will be used that eventually provide the covariance between the two sets. In VBA, the COVAR function works through the WorksheetFunction property.
In the end, you can set the values in the sheet, where you can see the first cell appears with the label “Covariance” and the second cell appears with the value calculated.
Notice: The cell ranges of X and Y values used here are hard-coded and if you really need to add flexibility in the macro, you can easily customize it.
Final Words:
In this post, you have learned how to calculate Covariance in Excel with the help of simple procedures. The above-mentioned detail is not something you cannot make use of. Make sure you practice enough of the above methods so that you can be a pro-Excel user.
