How to Create a Covariance Matrix in Excel?

Covariance is a statistical measure that shows the direction of the relationship between two variables. It tells whether the variables move together (positive) or move in opposite directions (negative). However, covariance only indicates the direction of the relationship, not how strong the relationship is.

UNDERSTANDING COVARIANCE

• If two variables tend to increase or decrease together, they have a positive covariance.
• If one variable increases while the other decreases, they exhibit a negative covariance.

COVARIANCE FORMULA

The mathematical formula for Covariance of a population is given as:

Cov(x,y) = \frac{\sum_{i=1}^{n} (x_i-\overline{x})(y_i-\overline{y})}{n}  

Where x,y is the array of first and second variable respectively, \overline{x}  and \overline{y}  are the mean values of x and y respectively and n is the no. of elements in the array.

On the other hand, the mathematical formula for Covariance of a sample is given as:

Cov(x,y) = \frac{\sum_{i=1}^{n} (x_i-\overline{x})(y_i-\overline{y})}{n-1}

Covariance values range from (-\infty ,\infty) .The actual number is not very important because it depends on the units of the data. Instead, the sign of covariance matters: a positive value means the variables move in the same direction, while a negative value means they move in opposite directions.

What is a Covariance matrix?

A Covariance matrix is a square, symmetric matrix that displays the covariance between each pair of variables in a dataset. The diagonal elements represent the variance of each individual variable, and the off- diagonal elements show the covariance between variable pairs.

Example Use Case in Excel

Suppose we have marks scored by several students across different subjects. We want to analyze how the scores in one subject relate to those in another using a covariance matrix.

Steps to Create a Covariance Matrix in Excel

We can create a covariance matrix in Excel using the Covariance function present inside the data analysis tool available under the data analysis the toolpak add-in package.

"Suppose we have a group of students, and we want to create a covariance matrix to analyze the relationship between their scores in various subjects. The dataset contains marks obtained by each student across different subjects, as shown below:"

Step 1: Click the Data ribbon in the excel menu and select the Data Analysis tool option.

Step 2: A data analysis tool dialog box will appear on the screen. From all the available options in the dialog box, select the Covariance option and click OK.

Step 3: A Covariance dialog box will appear. In the Input Range, select the data array (B1:D7) since we are comparing marks. Because the data is arranged in columns, choose Columns under Grouped by and check Labels in first row. To display the covariance matrix in the same worksheet, enter the desired cell (A10) in the Output Range, then click OK.

Step 4: The covariance matrix will get generated from the A10 cell as shown in the figure below. 

So this is how we create a covariance matrix in Excel.