The PEARSON function in Excel is used to calculate the Pearson correlation coefficient, which measures the strength and direction of the linear relationship between two sets of data. Here’s how you can use it:
Syntax
=PEARSON(array1, array2)
- array1: The first data set (a range of values).
- array2: The second data set (a range of values) of equal length to array1.
Steps to Use the PEARSON Function
- Prepare your data: Ensure you have two sets of numerical data organized, typically in two columns.
- Select the cell for the result: Click on the cell where you want the Pearson correlation coefficient to appear.
- Enter the function:
- Type `=PEARSON(`.
- Highlight the range for your first data set (array1) or type its reference.
- Type a comma.
- Highlight the range for your second data set (array2) or type its reference.
- Type a closing parenthesis `)`.
- Press Enter: The calculated Pearson correlation coefficient will appear in the selected cell.
Example
Assume that you have data in columns A and B from rows 1 to 10. To calculate the Pearson correlation coefficient for this data:
- Column A (array1): `A1:A10`
- Column B (array2): `B1:B10`
In the cell where you want the result, enter the formula:
=PEARSON(A1:A10, B1:B10)
After typing the formula, press Enter. The cell will display the correlation coefficient, which ranges from -1 to 1. A value closer to 1 indicates a strong positive correlation, whereas a value closer to -1 indicates a strong negative correlation. A value around 0 suggests no linear correlation.
Important Notes
- The two ranges (array1 and array2) must be the same size; otherwise, Excel will return an error.
- The function only works with numerical data; non-numeric values will be ignored.
- The PEARSON function and the correlation coefficient it calculates assume a linear relationship between the data sets. Non-linear relationships won’t be accurately represented by this value.
Unlock Your Potential
Excel
Access
Power BI
