PEARSON Function in Excel calculates the Correlation Coefficient for two sets of values.
PEARSON CORRELATION COEFFICIENT FORMULA:Pearson 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 PEARSON Function in Excel:
Where array1 is a set of independent variable and array2 is a set of dependent variable.
Example of PEARSON Function in Excel:
The column X and Y contains the two array values. Pearson correlation coefficient of these values can be calculated using formula
=PEARSON( A2:A15, B2:B15 )
as shown in the above example. This result in the value of 0.89871, 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
- #N/A – Occurs if the supplied arrays are of different lengths.