PEARSON Function

Basic Description

The Excel PEARSON function calculates the Pearson Product-Moment Correlation Coefficient for two sets of values.

Syntax: PEARSON( 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.

Excel Pearson Function vs. Excel Correl Function

The Excel Pearson function is the same as the Excel Correl 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 than Excel 2003, you should use the Correl function in preference to the Pearson function. In later versions of Excel, both functions should give the same results.

Pearson Function Example

1 x y
2 1 10.11
3 2 22.90
3 2 27.61
4 3 27.61
5 4 11.15
6 5 31.08
7 6 37.90
8 7 33.49
9 8 21.05
10 9 27.01
11 10 45.78
12 11 31.32
13 12 50.57
14 13 45.48
15 14 40.94
16 15 53.76
17 16 36.18
18 17 49.77
19 18 55.66
20 19 63.83
21 20 63.60

Columns A and B of the spreadsheet on the left contain two arrays of values. These are shown in the chart below:

Positive Correlation

The Pearson Product-Moment Correlation Coefficient of the values in columns A and B of the spreadsheet can be calculated using the Excel Pearson function, as follows:

=PEARSON( A2:A21, B2:B21 )

This gives the result 0.870035104, which indicates a strong positive correlation between the two sets of values. Information on interpreting the significance of the Pearson Product-Moment Correlation Coefficient is given on the University of Connecticut website


Pearson Function Errors

If you get an error from your Excel Pearson function this is likely to be one of the following :

#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

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>



clearPost Comment