CORREL Function in Excel – Calculate correlation coefficient in Excel

CORREL Function in Excel calculates the Correlation Coefficient for two sets of values

CORRELATION COEFFICIENT FORMULA:

correlation coefficient for two sets of values, x and y, is given by the formula:

CORREL Function in Excel 1

where x and y are the sample means of the two arrays of values.

If the resultant value – r is close to +1, this indicates a strong positive correlation

If the resultant value – r is close to -1, this indicates a strong negative correlation

 

Syntax of CORREL Function in Excel (CORRELATION Function):

CORREL( array1, array2 )

Where array1 is a set of independent variable and array2 is a set of dependent variable.

 

Example of CORREL Function in Excel (CORRELATION Function):

CORREL Function in Excel 2

 

The column X and Y contains the two array values. Correlation Coefficient of these values can be calculated using formula

=CORREL( A2:A15, B2:B15 )

as shown in the above example. This result in the value of 0.914499, which indicates a strong positive correlation between the two sets of values.

When we plot x and y we get the plot as shown below

CORREL Function in Excel 3

NOTE:

  • #N/A – Occurs if the supplied arrays are of different lengths.

previous CORREL Function in Excel                                                                                                                next CORREL Function in Excel