What is the PEARSON Function?
The PEARSON function is categorized under Excel Statistical functions. It will calculate the Pearson Product-Moment Correlation Coefficient for two sets of values. For example, we can find out the relationship between the age of a person and the appearance of grey hair.
The PEARSON function uses the following arguments:
- Array1 (required argument) – This is the number set of independent values.
- Array2 (It is a required argument) – This is the set of dependent values.
The function ignores text values and logical values that are supplied as part of an array. The Pearson product-moment correlation coefficient for two sets of values, x and y, is given by the formula:
Where x and y are the sample means of the two arrays of values.
If the value of r is close to +1, it indicates a strong positive correlation, and if r is close to -1, it denotes a strong negative correlation.
How to use PEARSON Function in Excel?
To understand the uses of the PEARSON function, let’s consider an example:
Suppose we are given the following sets of data and we wish to find out the correlation between them:
The formula to use is:
We get the result below:
The result above implies a positive correlation among the data sets.
Things to remember about the PEARSON Function
- #N/A! error – Occurs if the given array arguments are of different lengths.
- #DIV/0! error – Occurs when either of the given array arguments is empty or when the standard deviation of their values is equal to zero.
- The PEARSON function performs the same calculation as the CORREL function. However, in MS Excel 2003 and earlier versions, PEARSON may exhibit some rounding errors.
- If an array or reference argument contains text, logical values, or empty cells, the values are ignored. However, cells with the value zero are included by this function.
Thanks for reading CFI’s guide to important Excel functions! By taking the time to learn and master these functions, you’ll significantly speed up your financial modeling. To learn more, check out these additional CFI resources: