The PEARSON function returns the Pearson-product moment correlation coefficient, r, a dimensionless index that ranges from -1.0 to 1.0 inclusive and reflects the extent of a linear relationship between two data sets.




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 PEARSON function is the same as the CORREL function – both functions should produce the same results.


  A B C D E F
1 Array1 Array2   Formula Result Notes
2 3 8   =PEARSON(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 array arguments have 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.