The Excel CORREL Function
Pearson Product-Moment Correlation Coefficient
The Pearson product-moment correlation coefficient is a statistical measurement of the correlation (linear association) between two sets of values.
The Pearson product-moment 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 value of r is close to +1, this indicates a strong positive correlation, and if r is close to -1, this indicates a strong negative correlation.
Further information on the Pearson Product-Moment Correlation Coefficient is provided on WikipediaFunction Description
The Excel Correl function calculates the Pearson Product-Moment Correlation Coefficient for two sets of values.
The syntax of the function is:
CORREL( array1, array2 )
Where array1 is a set of independent variables and array2 is a set of dependent variables. These arrays should be of equal length.
Note that the Correl function ignores text values and logical values that are supplied as part of an array.
Excel Correl Function vs. Excel Pearson Function
The Excel Correl function is the same as the Excel Pearson Function, except that, in earlier versions of Excel (earlier than Excel 2003), the Pearson function may exhibit some rounding errors.
Therefore, if you are using an earlier version of Excel, you should use the Correl function in preference to the Pearson function. In later versions of Excel, both functions should give the same results.
Excel Correl Function Example
| A | B | |
|---|---|---|
| 1 | x | y |
| 2 | 2 | 22.90 |
| 3 | 10 | 45.78 |
| 4 | 7 | 33.49 |
| 5 | 17 | 49.77 |
| 6 | 14 | 40.94 |
| 7 | 16 | 36.18 |
| 8 | 8 | 21.05 |
| 9 | 12 | 50.57 |
| 10 | 11 | 31.32 |
| 11 | 15 | 53.76 |
| 12 | 18 | 55.66 |
| 13 | 3 | 27.61 |
| 14 | 4 | 11.15 |
| 15 | 1 | 10.11 |
| 16 | 6 | 37.90 |
| 17 | 5 | 31.08 |
| 18 | 13 | 45.48 |
| 19 | 19 | 63.83 |
| 20 | 20 | 63.60 |
| 21 | 9 | 27.01 |
Columns A and B of the above spreadsheet on the right contain two arrays of values. These are plotted in the chart below:
The Pearson Product-Moment Correlation Coefficient of the values in columns A and B of the spreadsheet can be calculated using the Excel Correl function, as follows:
=CORREL( A2:A21, B2:B21 )
This gives the result 0.870035104, which indicates a strong positive correlation between the two sets of values.
The University of Connecticut website provides a useful table showing the significance of the Pearson Product-Moment Correlation Coefficient.
For further details and examples of the Excel Correl function, see the Microsoft Office website.
Correl Function Errors
If you get an error from your Excel Correl function this is likely to be one of the following:
Common Errors
| #N/A | - | Occurs if the supplied arrays are of different lengths. |
| #DIV/0! | - | Occurs if either of the supplied arrays are empty or if the standard deviation of their values equals zero. |