Excel CORREL Function to calculate coefficient of correlation

This Excel tutorial explains how to use Excel CORREL Function to calculate coefficient of correlation.

Coefficient of Correlation

Covariance is a measure of how much two random variables change together. After calculating covariance, we can check the sign whether it is negative or positive.  Positive covariance means positive relationship (y increases as x increases), negative covariance means a negative relationship (y decreases as x increases).

In order to measure the strength of relationship,the next step is to calculate Coefficient of Correlation using Covariance. Coefficient of Correlation ranges between -1 and 1. Again, positive sign indicates positive relationship while negative sign indicates negative relationship. In addition, 1 indicates the strength of linear relationship is very strong, 0 indicates no linear relationship.

Formula of Population coefficient of correlation:  (σ is the standard deviation, σxy is the covariance)

ρ = σxy / (σx * σy)

Sample coefficient of correlation:

r = Sxy / (Sx * Sy)

Coefficient of Correlation – Manual Calculation

Assume B2 to B4 are the source data of variable x and y, x increases with y. Prepare some calculation as below.

Excel CORREL Function to calculate coefficient of correlation 01

Population coefficient of correlation = σxy / (σx * σy)

= (35/3) / (2.16*5.72)

= 0.9449

Sample coefficient of correlation =  Sxy / (Sx * Sy)

= (35/2) / (2.646*7)

= 0.9449

 

If x increases as y deceases, then

Excel CORREL Function to calculate coefficient of correlation 02

Population coefficient of correlation = σxy / (σx * σy)

= (-35/3) / (2.16*5.72)

= -0.9449

Sample coefficient of correlation =  Sxy / (Sx * Sy)

= (-35/2) / (2.646*7)

= -0.9449

 

If y is a constant then

Excel CORREL Function to calculate coefficient of correlation 03

Population coefficient of correlation = σxy / (σx * σy)

= (0/3) / (2.16*0)

= 0

Sample coefficient of correlation =  Sxy / (Sx * Sy)

= (0/2) / (2.646*0)

= -0

Coefficient of Correlation – Excel

We can use CORREL Function to calculate coefficient of correlation.

Syntax of CORREL

CORREL(array1, array2)

array1 is the range of variable x, while array2 is the range of variable y

Example

Excel CORREL Function to calculate coefficient of correlation 01

Correlation =CORREL(B2:B4,C2:C4)

= 0.944911183

 

Leave a Reply

Your email address will not be published. Required fields are marked *