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.
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
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
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
Correlation =CORREL(B2:B4,C2:C4)
= 0.944911183
