Excel CORREL Function

What is CORREL function in Excel?

The CORREL function is one of the Statistical functions of Excel.

It Returns the correlation coefficient between two data sets.

We can find this function in Statistical category of the insert function Tab.

How to use CORREL function in excel

1. Click on an empty cell (like F5).

2. Click on the fx icon (or press shift+F3).

3. In the insert function tab you will see all functions.

4. Select STATISTICAL category.

5. Select CORREL function.

6. Then select ok.

7. In the function arguments Tab you will see CORREL function.

8. Array1 is a cell range of values. The values should be numbers, names, arrays, or references that contain numbers.

9. Array2 is a second cell range of values. The values should be numbers, names, arrays, or references that contain numbers.

10. You will see the results in the formula result section.

Examples of CORREL function in Excel

example1:

what is the formula for the correlation coefficient in excel?

Here is the formula for the correlation coefficient:

example 2:

How to calculate the correlation coefficient in Excel?

=SUM((B2:B10-AVERAGE(B2:B10))*(C2:C10-AVERAGE(C2:C10)))/SQRT(SUM((B2:B10-AVERAGE(B2:B10))^2)*SUM((C2:C10-AVERAGE(C2:C10))^2))

The process for finding the mean absolute deviation involves the following steps.

1.get numbers.

Age=25,25,27,22,29,29,21,25,22
Height=210,205,195,198,199,201,202,200,204

2. calculate average of numbers.

=AVERAGE(B2:B10)----->>>>answer is  25
=AVERAGE(C2:C10)----->>>>answer is  201.55

3. calculate distance from average of numbers.

=AVERAGE(B2:B10)-B11----->>>>answer is 
 0,0,2,-3,4,4,-4,0,-3
=AVERAGE(B2:B10)-C11----->>>>answer is  8.4,3.4,-6.5,-3.5,-2.5,-0.5,0.4,-1.5,2.4

4. calculate the power of distance from average of numbers.

=(B2:B10-AVERAGE(B2:B10))^2----->>>>answer is 
 0,0,4,9,16,16,16,0,9
=(C2:C10-AVERAGE(C2:C10))^2----->>>>answer is  71.3,11.86,42.97,12.64,6.53,0.3,0.19,2.415.97

5. calculate the product of distance from average of numbers.

=(AVERAGE(B2:B10)-B11) *(AVERAGE(B2:B10)-C11)----->>>>answer is 
 0,0,-13.1,10.6,-10.2,-2.2,-1.7,0,-7.3

6. Finally calculate the correlation coefficient.

=SUM((B2:B10-AVERAGE(B2:B10))*(C2:C10-AVERAGE(C2:C10)))/SQRT(SUM((B2:B10-AVERAGE(B2:B10))^2)*SUM((C2:C10-AVERAGE(C2:C10))^2))----->>>>answer is -0.23

All these steps are summarized in the following function.

=CORREL(B2:B10,C2:C10) ----->>>>answer is -0.23

Errors in CORREL function

If you enter a non-numeric number, the output show #DIV/0! or #NAME? error

CORREL(a,b) ----->>>>answer is   #NAME?

CORREL("a","b") ----->>>>answer is  #DIV/0!