PEARSON Function
Returns the Pearson correlation coefficient between two sets of numbers.
Category
Statistical
Available in
All versions, Microsoft 365
Returns
A number from −1 to 1
Overview
PEARSON returns the Pearson product-moment correlation coefficient, r — the standard measure of how closely two sets of numbers follow a straight line. It gives exactly the same result as CORREL.
1 is a perfect rising line, −1 a perfect falling line and 0 no straight-line relationship. Rows where either value isn't a number are left out in pairs.
Syntax
Arguments
| Argument | Required? | What it does |
|---|---|---|
| array1 | Yes | The independent values (for example, ad spend). |
| array2 | Yes | The dependent values, the same size as array1. |
Examples
Example 1 — Same answer as CORREL
The ad spend and sales data again.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Month | Ad spend ($k) | Sales ($k) | Measure | Result |
| 2 | Jan | 2 | 21 | PEARSON | 0.9925 |
| 3 | Feb | 3 | 25 | CORREL | 0.9925 |
| 4 | Mar | 5 | 33 | ||
| 5 | Apr | 4 | 28 | ||
| 6 | May | 6 | 38 | ||
| 7 | Jun | 7 | 40 | ||
| 8 | Jul | 8 | 46 | ||
| 9 | Aug | 5 | 31 |
Example 2 — Built from covariance
r is the covariance divided by both standard deviations.
Returns the same 0.9925.
Common mistakes
- #DIV/0! One of the ranges has no spread, or there are fewer than two pairs.
- #N/A The ranges are different sizes.
Notes
- Related functions: CORREL, RSQ, COVARIANCE.S.

Author
Kyle Stott
Certified Microsoft Excel Expert
Kyle has worked professionally with Microsoft Excel for over a decade and has consulted on and taught Excel best practices to over 400 companies across 30+ countries.