How to Use the CORREL Function in Excel

This guide explains how to use CORREL Function in Excel, including its syntax, required arguments, practical examples, and common errors.

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:

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

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

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

- 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!