# 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. 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

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

= 0.944911183

Wyman is a Human Resources professional based in Hong Kong, specialized in business analysis, project management, data transformation with Access and Excel.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist in Access / Excel
- Microsoft Specialist in MS Project
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist