CORREL

The CORREL function returns the Pearson correlation coefficient between two data sets.

This coefficient shows how much a change in the values of set X affects the values of set Y and takes values from −1 to 1.

Syntax

CORREL(data_X, data_Y)

Argument	Description	Permitted values
data_X	First array or range of cells	Any numerical values, ranges of cells with numbers. Text values or empty cells without numbers are not allowed
data_Y	Second array or range of cells	Similar to the first argument

Examples of use

Calculating the correlation coefficient between two data sets

=CORREL(A1:A10, B1:B10)

This formula will return the correlation coefficient between the data in the range A1:A10 and B1:B10.

If you have two columns of data:

• Column A (A1:A5): 1, 2, 3, 4, 5.
• Column B (B1:B5): 2, 4, 6, 8, 10.

=CORREL(A1:A5, B1:B5)

This formula will return 1, since there is a perfect positive linear relationship between these variables.

Let's consider the opposite example:

• Column A (A1:A5): 1, 2, 3, 4, 5.
• Column B (B1:B5): 10, 8, 6, 4, 2.

=CORREL(A1:A5, B1:B5)

This formula will return −1, since there is a perfect negative linear relationship between the variables.

Notes

• If the arrays have a different number of elements, the function will return the #N/A error.
• The correlation coefficient does not indicate a causal relationship; it only shows the degree of linear dependence.
• Text values in range cells will be ignored .