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.