PEARSON in Excel: Product-Moment Correlation and Interpretation Checks

• Introduced: first introduction version is not established by the official history reviewed; the current product list is not a launch record.
• Availability: official help lists Excel for Microsoft 365, Excel for Microsoft 365 for Mac, Excel 2024, Excel 2024 for Mac, Excel 2021, Excel 2021 for Mac, Excel 2019, Excel 2016.
• Checked: October 14, 2026; official PEARSON reference. Documentation and independently modeled examples; no native Excel execution or broader platform certification.

=PEARSON(array1, array2)

Both inputs are required. Microsoft’s equation divides the sum of paired centered products by the square root of the product of the two centered sums of squares.

The reference says to ignore text, logical values and empty reference cells while including zero. It assigns #N/A when both arrays are empty or the data-point counts differ. This wording is retained specifically for PEARSON rather than borrowing CORREL’s different empty-array statement.

Enter X 1,2,3,4 in A2:A5 and Y 5,8,11,14 in B2:B5.

=PEARSON(A2:A5,B2:B5)

Expected result: 1. The Y values follow Y=3*X+2. The means are 2.5 and 9.5, the centered-product sum is 15, and the centered sums of squares are 5 and 45. The equation gives 15/SQRT(5*45)=1.

The slope is 3, while the correlation is 1. They are not interchangeable. A different positive slope can still give correlation 1 for an exact straight line.

Enter 11,8,5,2 in C2:C5, following Y=14-3*X.

=PEARSON(A2:A5,C2:C5)

Expected result: -1. The centered-product sum is -15 and the denominator is still 15. The sign indicates decreasing rather than increasing linear association.

For the less orderly values 2,1,4,3 in D2:D5, the same calculation gives 0.6. Here both centered sums of squares are 5 and the numerator is 3. Every pair must be kept together when sorting the record table.

Multiply the original increasing-line Y values by 100. The new values are 500,800,1100,1400, but PEARSON remains 1. Adding 20 to every X value also preserves the coefficient.

A unit conversion can greatly change covariance while leaving correlation unchanged. This is why the coefficient is suitable for describing linear direction and strength across units, but not for estimating how many Y units accompany one X unit.

For the imperfect D2:D5 relationship, multiplying all Y values by -2 changes 0.6 to -0.6. The magnitude stays the same and the direction reverses.

Enter X -2,-1,0,1,2 in F2:F6 and Y 4,1,0,1,4 in G2:G6.

=PEARSON(F2:F6,G2:G6)

Expected result: 0. X has mean 0; Y has mean 2. The centered products are -4,1,0,-1,4, which sum to zero. Both variables still vary, so this zero is a defined linear coefficient.

Yet Y is exactly X squared. A scatter plot would show a curve; zero correlation alone does not show independence. Nor does a coefficient near 1 prove causation or supply a significance test.

For a constant input, one centered sum of squares is zero, making the mathematical denominator zero. The dedicated PEARSON page reviewed does not explicitly name that native error result, so this guide does not certify its error display by analogy with CORREL.

Mixed errors, missing values in different rows, singleton inputs and large-magnitude numerical stability are outside the executed evidence. Use complete paired numeric records for reproducing these calculations.

CORREL documents the same coefficient equation; that does not guarantee identical behavior for every invalid-input case or floating-point implementation. COVARIANCE.S and COVARIANCE.P answer related, unit-sensitive questions.

• Microsoft PEARSON reference.
• Editorial testing standards.

Expected results are independent statistical/arithmetic models or explicitly documented rules, not captured native output. Examples use English function names and comma separators. Datasets and reporting policies are fictional.