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

Want to practice PEARSON in a real spreadsheet? Open a blank sheet in our Practice Sandbox and try it with live data, with the AI Excel tutor beside you. No Excel needed — just a free account.Try it free in the Sandbox

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

=PEARSON(array1, array2) Explain

Arguments

ArgumentRequired?What it does
array1YesThe independent values (for example, ad spend).
array2YesThe dependent values, the same size as array1.

Examples

Example 1 — Same answer as CORREL

The ad spend and sales data again.

E2fx=PEARSON(B2:B9,C2:C9)
ABCDE
1MonthAd spend ($k)Sales ($k)MeasureResult
2Jan221PEARSON0.9925
3Feb325CORREL0.9925
4Mar533
5Apr428
6May638
7Jun740
8Jul846
9Aug531
=PEARSON(B2:B9,C2:C9) Explain

Example 2 — Built from covariance

r is the covariance divided by both standard deviations.

=COVARIANCE.S(B2:B9,C2:C9)/(STDEV.S(B2:B9)*STDEV.S(C2:C9)) Explain

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

Kyle Stott

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.