The following article introduces you to the PEARSON function - one of the functions in the statistical function group is very popular in Excel.
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!