Excel calculate Covariance, Coefficient of Correlation

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)

Covariance

Calculate Covariance in Excel

There are two Functions for Covariance:

Covariance.P (array1, array2) - Used for Population Covariance
Covariance.S (array1, array2)  - Used for Sample 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).

Correlation-r

Coefficient of Correlation ranges between -1 and 1.

r = -1 : Perfect Negative Correlation

r = 0 : No Correlation

r = 1 : Perfect Positive Correlation

Correlation-Levels

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

Outbound References

https://support.office.com/en-us/article/COVARIANCE-P-function-6f0e1e6d-956d-4e4b-9943-cfef0bf9edfc?ui=en-US&rs=en-US&ad=US

Leave a Reply

Your email address will not be published.