This Excel tutorial explains the meaning and calculation of Covariance and Coefficient of Correlation.

You may also want to read:

Excel Range, Variance, Standard Deviation

## Covariance, Coefficient of Correlation

Assume that we have two sets of data – English and Mathematics results for each student. How can we tell whether English result has any relationship with Mathematics result?

Name of Student | English Result | Math Result |

John | 50 | 60 |

Mary | 60 | 70 |

Peter | 70 | 80 |

To answer the question, we need **Covariance** and **Coefficient of Correlation**, which measure the linear relationship of two variables.

## Covariance

The purpose of **Covariance** is to measure the direction of the relationship, whether the relationship is positively correlated (x increases when y increases) or negatively correlated (x decreases when y increases).

The value of **Covariance** does not make much sense; lets say the **Covariance** is 100, does it mean the correlation is very strong? No, because it is not comparing to any value.

We only need to know whether it is positive or negative, **Covariance** is more important for further calculation of **Coefficient of Correlation** (we will discuss below).

Below is the formula of **Sample Covariance.** (Similar to Standard Deviation, replace N with n-1 for **Population Covariance**)

#### Calculate Covariance in Excel

There are two Functions for Covariance:

Covariance.P (array1, array2) - Used forPopulation Covariance

Covariance.S (array1, array2) - Used forSample Covariance

Using the example of English result and Math result

Formula | Result |

=COVARIANCE.P(B2:B4,C2:C4) | 66.66667 |

## Coefficient of Correlation

**Coefficient of Correlation** measures the relative strength of the linear relationship between two variables. Put it simply, it is a numerical value to measure how strong the relationship is. The larger the value, the stronger the relationship.

**Coefficient of Correlation** is denoted by a Greek symbol rho, it looks like letter r. To calculate **Coefficient of Correlation**, divide **Covariance** by **Standard Deviation** of two variables (Sx, Sy).

Coefficient of Correlation ranges between -1 and 1.

r = -1 : Perfect Negative Correlation

r = 0 : No Correlation

r = 1 : Perfect Positive Correlation

#### Calculate Coefficient of Correlation in Excel

Correl (array1, array2)

Using the example of English result and Math result

Formula | Result |

=CORREL(B2:B4,C2:C4) | 1 |