Excel functions / Statistics
PEARSON function
The Pearson correlation coefficient, the same as CORREL.
Syntax
=PEARSON(array1, array2)
Arguments
array1: The independent values.array2: The dependent values, the same size as array1.
When to use it
PEARSON gives the correlation coefficient between two sets of numbers: 1 when they rise together in a straight line, -1 when one falls as the other rises, 0 when there is no straight-line relation. It gives the same result as CORREL.
Examples
Ad spend against sales
A B C D 1 Ad spend Sales 2 100 1200 0.993 3 200 1900 4 300 3100 5 400 3800 6 500 5200 In D2:
=PEARSON(A2:A6, B2:B6)→ 0.993R squared: how much of the variation the line explains
A B C D 1 Ad spend Sales 2 100 1200 99% 3 200 1900 4 300 3100 5 400 3800 6 500 5200 In D2:
=PEARSON(A2:A6, B2:B6)^2→ 99%
Common mistakes
- Correlation is not cause. A high correlation says two things move together, not that one causes the other.
PEARSON in other languages
Excel shows function names in its own language. In these languages it also puts a semicolon between arguments where English has a comma.
- Czech: PEARSON
- Danish: PEARSON
- Dutch: PEARSON
- Finnish: PEARSON
- French: PEARSON
- German: PEARSON
- Hungarian: PEARSON
- Italian: PEARSON
- Norwegian: PEARSON
- Polish: PEARSON
- Portuguese (Brazil): PEARSON
- Portuguese (Portugal): PEARSON
- Russian: PEARSON
- Spanish: PEARSON
- Swedish: PEARSON
- Turkish: PEARSON
Related functions
- CORREL: The correlation coefficient of two data sets: from −1 (opposite) through 0 (unrelated) to 1 (move together).
- RSQ: R squared: the share of the variation in y that the best-fit line explains, from 0 to 1.
- SLOPE: The slope of the straight line that best fits the data: how much y changes for each 1 added to x.
- COVARIANCE.S: Sample covariance: like COVARIANCE.P, but divides by n − 1 for a sample.