How to Use the PEARSON Function in Excel

This guide explains how to use PEARSON Function in Excel, including its syntax, required arguments, practical examples, and common errors.

Description: The function returns the Pearson product moment correlation coefficient, r with a scalar index in the range of -1 to 1. The function reflects the linear relationship expansion between the two data sets.

Syntax: PEARSON (array1, array2)

Inside:

- array1: Independent value set, required parameter .

- array2: Set of dependent values, required parameters .

Attention:

- The value of the argument must be a number, name or reference array containing numbers.

- If the argument is a reference or an array containing logical values or text -> these values are ignored, but the value 0 is still counted.

- If array1, array2 contains different data points -> the function returns the # N / A error value .

- The formula for Pearson product moment correlation coefficient, r is:

[r = frac {{sum {left ({x - overline x} right) left ({y - overline y} right)}}} {{sqrt {sum {{{left ({x - overline x} right)} ^ 2} sum {{{left ({y - overline y} right)} ^ 2}}}}}}]

Inside:

x and y are the samples AVERAGE (array1) and AVERAGE (array2)

For example:

Find the Pearson product moment correlation coefficient, r of the two data sets in the data table below:

- In the cell to calculate enter the formula : = PEARSON (C6: C10, D6: D10)

- Press Enter -> Pearson product moment correlation coefficient, r of 2 data sets is:

- In case the number of data points of 2 different data sets, for example, here the independent value set has 5 elements but the dependent value set has only 3 elements -> the function returns the # N / error value A.

Above are instructions and some specific examples when using PEARSON function in Excel.

Good luck!