The CORREL function returns the correlation coefficient, the Pearson Product-Moment Correlation Coefficient, for two sets of values.

Syntax

=CORREL(array1,array2)

Arguments

Argument Description
array1 A range of cells containing a set of independent variables
array2 A range of cells containinga set of dependent variables

Note: The CORREL function is the same as the PEARSON function. Both functions should produce the same results.

Examples

  A B C D E F
1 Data1 Data2   Formula Result Notes
2 3 8   =CORREL(A2:A6,B2:B6) 0.83205 Correlation coefficient of the two data sets in columns A and B
3 2 7        
4 5 10        
5 4 14        
6 6 18        

Usage note: Use the correlation coefficient to determine the relationship between two properties. For example, you can examine the relationship between a location’s average temperature and the use of air conditioners.

Common Function Error(s)

Problem What went wrong
#N/A Occurs if the supplied arrays are of different lengths
#DIV/0! Occurs if:

  either of the supplied arrays are empty
  the standard deviation of the array values equals zero

The Pearson product-moment correlation coefficient is a statistical measurement of the correlation, linear association, between two sets of values, x and y.

The equation for the correlation coefficient is:    

    \[    r = \frac {\sum (x - \bar{x})(y - \bar{y})}{ \sqrt{ \sum (x - \bar{x})^2 \sum (y - \bar{y})^2 }}    \]

where \overline{x} and \overline{y} are the sample means AVERAGE(array1) and AVERAGE(array2).

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.

See Wikipedia for more information on the Pearson Product-Moment Correlation Coefficient.