**Covariance **refers to the measurement of how one variable defers to another. The variables do not have to be dependent on one another. The formula for calculating covariance is represented in the following image.

** X_{i}** = Data value of the first category

** Y_{i }**= Data value of the second category

** X̄ **= Mean data Value of the first category

** Ȳ **= Mean data Value of the second category

** n **= Total number of data values

We’ll create two matrices with two categories each and use the covariance command in **Excel **to calculate the deviations. We’ll use the ** Data Analysis** ribbon from the

**Data**tab to do this.

We have a dataset of scores in three subjects.

### Step 1 – Apply the Data Analysis Command in Excel

- Click on the
tab.*Data* - From the
**Analysis**group, select thecommand.*Data Analysis*

### Step 2 – Select the Covariance Option from Analysis Tool

- From the
list, select the*Analysis Tools*option.*Covariance* - Click
.*OK*

### Step 3 – Select the Range to Calculate Covariance Matrix in Excel

- To calculate variance with
**Math**,**Science**, and**History**, select the**Input Range****B4:D13**alongside the**Header**. - Select
**Labels in first row box**.

- For
**Output Range**, select any cell (**B15**). - Click
.*OK*

- The covariances will appear as in the image shown below.

## How to Interpret the Covariance Matrix in Excel

### Case 1 – Covariance for a Single Variable

- The variance of
with its mean is*Math*.**137.654321** - The variance of
is*Science*.**95.1111** - The variance of
is*History***51.5555.**

### Case 2 – Covariance for Multiple Variables+

- The variance value between
and*Math*is*Science*.**45.85185** - The variance value between
and*Math*is*History*.*-27.3703* - The variance value between
and*Science*is*History*.**86.66667**

## Positive Covariance

The presence of **positive covariance** indicates that the two variables are proportionate. When one variable rises, the other tends to rise with it. As in our example, the covariance between ** Math **and

**is positive (**

*Science***), implying that students who perform well in**

*45.85185***also perform well in**

*Math***.**

*Science*## Negative Covariance

**Negative covariance**, in contrast to positive covariance, means that when one variable increases, the other decreases. The covariance between ** Math **and

**in our example covariance is negative (**

*History***), indicating that students who score higher in**

*-27.3703***will score lower in**

*Math***.**

*History*** Notes: **

If you cannot find the ** Data Analysis** tool in your

**tab, you may need to activate the**

*Data***first.**

*Data Analysis ToolPak*- Go to
.*Home* - Click on
.*Options*

- From
, select the*Excel Options*options.*Add-ins* - Click the
option.*Analysis ToolPak* - Click
.*OK*

- Go to the
tab.*Developer* - From
, click on the*Add-ins***Excel Add-ins**command.

- Select the
from the list.*Analysis ToolPak* - Click
to add the*OK*.*Add-in*

- You will find the
command in your*Data Analysis*tab.*Data*

**Download the Practice Workbook**

**<< Go Back to Excel for Math | Learn Excel**