Understanding CORREL Function in Excel

This article introduces the CORREL function, one of the statistical functions widely used in Excel.

Description: It returns the correlation coefficient between 2 data sets. The correlation coefficient is used to determine the relationship between 2 attributes.

Syntax: CORREL(array1, array2)

Important Note:

- array1: Range containing values of the first dataset.

- array2: Range containing values of the second dataset.

Please Note:

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

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

- If array1 and array2 are empty or the standard deviation of values in both datasets equals 0 -> the function returns the error value #DIV/0!

- The function equation used is:

The function equation used is:

Where:

¯¯¯x$\overline{x}$ and ¯¯¯y$\overline{y}$: Are the sample mean values AVERAGE(array1) and AVERAGE(array2).

Example:

Calculate the correlation coefficient between 2 datasets in the table below:

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

- Press Enter -> the correlation coefficient between the 2 datasets is:

- If 1 of the 2 datasets contains empty values -> the function returns an error value #DIV/0!

In the case where size = 1, the function returns an error value #NUM!

Here is the guide and some specific examples of using the CORREL function in Excel.

Wishing you success!