PEARSON Function
Pearson correlation coefficient between two arrays (same as CORREL).
Syntax
Parameters
| Parameter | Description | Required |
|---|---|---|
| array1 | The first set of independent (or paired) numeric values. | Required |
| array2 | The second set of numeric values, the same length as array1. | Required |
Basic Example
Correlation between two paired series
=PEARSON({2,3,5,7,9},{1,2,3,4,5})Identical to CORREL on the same data; the coefficient is near 1, a strong positive linear relationship.
Advanced Examples
Example 1: Correlation from ranges
Comparing temperature to ice-cream salesBoth ranges must have the same number of points
=PEARSON(B2:B13,C2:C13)Example 2: Verifying equivalence with CORREL
Two function names, one answerShow the two functions return the same value
=PEARSON(A2:A20,B2:B20) and =CORREL(A2:A20,B2:B20)How PEARSON Works
PEARSON computes r = Σ((x - x̄)(y - ȳ)) / sqrt(Σ(x - x̄)² · Σ(y - ȳ)²). The result is bounded by -1 and 1. It is mathematically and functionally identical to CORREL; text, logicals, and empty cells are ignored in each array.
Important Notes & Limitations
Measures only linear association, like CORREL; non-linear links may show ~0.
Both arrays must contain the same number of data points, or #N/A is returned.
If either array has zero variance, the function returns #DIV/0!.
Common Errors & Fixes
#N/AThe two arrays have different numbers of data points.Fix: Make sure array1 and array2 are the same length.
#DIV/0!One of the arrays has no variance (all values equal).Fix: Use data with variation, or check for constant columns.
Download Practice File
PEARSON
pearsonPractice PEARSON with Real Data
Download a sample CSV file with pre-populated data and practice exercises for the PEARSON function. Works in both Excel and Google Sheets.
File format: CSV (comma-separated values) - opens in Excel, Google Sheets, and all spreadsheet apps