CORREL function - The function returns the correlation coefficient between two data sets in Excel

The following article introduces you to the CORREL function - one of the functions in the statistical function group is very popular in Excel.

CORREL function - The function returns the correlation coefficient between two data sets in Excel Picture 1

Description: The function returns the correlation coefficient between two data sets. Correlation coefficient is used to determine the relationship between two attributes.

Syntax: CORREL (array1, array2)

Inside:

- array1: The range of cells containing the values ​​of the first dataset .

- array2: The range of cells containing the values ​​of the second dataset .

Attention:

- If data cells or references contain logical values, text or blank cells -> they are ignored.

- If array1 and array2 have different data points -> the function returns the # N / A error value .

- If array1 and array2 are empty or the standard deviation of the values ​​of 2 data sets is 0 -> the function returns the # DIV / 0 error value !

- The function equation used is:

[C {rm {or}} relleft ({X, Y} right) = frac {{sum {(x - overline x) left ({y - overline y} right)}}} {{sqrt {sum {{{ (x - overline x)} ^ 2} sum {{{(y - overline y)} ^ 2}}}}}}]

Inside:

({overline x}) and ({overline y}): The average value of samples AVERAGE (array1) and AVERAGE (array2).

For example:

Calculate the correlation coefficient between the two data sets in the table below:

CORREL function - The function returns the correlation coefficient between two data sets in Excel Picture 2

- In the cell to calculate enter the formula: = CORREL (C6: C10, D6: D10)

CORREL function - The function returns the correlation coefficient between two data sets in Excel Picture 3

- Press Enter -> the correlation coefficient between 2 data sets is:

CORREL function - The function returns the correlation coefficient between two data sets in Excel Picture 4

- Case 1 of 2 data sets contains blank values ​​-> the function returns the # DIV / 0 error value !

CORREL function - The function returns the correlation coefficient between two data sets in Excel Picture 5

- Where size = 1 -> the function returns the error value #NUM!

Above are instructions and some specific examples when using the CORREL function in Excel.

Good luck!