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:
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):
Where array1 is a set of independent variable and array2 is a set of dependent variable.
Example of CORREL Function in Excel (CORRELATION Function):
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
NOTE:
- #N/A – Occurs if the supplied arrays are of different lengths.