PEARSON in Google Sheets: Linear Correlation Without Losing Row Pairing

• Introduced: first introduction date is not established by the reviewed official history; no desktop release number is inferred.
• Availability: documented in the rolling Google Sheets service; this guide uses the web worksheet workflow.
• Checked: October 14, 2026; official PEARSON reference. Documentation and independent calculations, with no native Sheets execution or wider client certification.

=PEARSON(data_y,data_x)

Google names the first series dependent data and the second independent data. Both are required. Corresponding cells must describe the same observation. The page explicitly identifies PEARSON as synonymous with CORREL; it does not provide a complete invalid-input error table.

Google says text is ignored. These examples use complete numeric pairs because that short statement does not fully establish how different missing positions, Boolean values or source errors affect pairing.

The independent Pearson model divides the sum of centered paired products by the square root of the product of the two centered sums of squares. Both series must vary for that mathematical denominator to be nonzero.

Create this table in A1:B5:

=PEARSON(B2:B5,A2:A5)

Expected result: 0.6. Both means are 2.5. The centered-product sum is 3; each centered sum of squares is 5. Therefore the coefficient is 3/SQRT(5*5)=0.6.

For a visible audit, calculate the means in E2 and E3, then fill three helper columns with the X deviation squared, Y deviation squared and product of the two deviations. Their totals should be 5,5,3. Those totals expose the numerator and scale normalization instead of hiding everything behind one output cell.

Replace Y with 5,8,11,14, following Y=3*X+2:

=PEARSON(B2:B5,A2:A5)

Expected result: 1. The centered-product sum is 15; X and Y centered sums of squares are 5 and 45. The denominator is 15.

The line’s slope is 3, while its correlation is 1. Replacing Y with 11,8,5,2, following Y=14-3*X, gives expected -1. The sign indicates direction, and an exact nonconstant straight line has full linear association under this model.

Restore the original Y values 2,1,4,3. Reverse the order of both columns together: X becomes 4,3,2,1 and Y becomes 3,4,1,2. The expected coefficient stays 0.6.

Sort Y alone into 1,2,3,4 while leaving X untouched, and the coefficient becomes 1. That operation changed which values belong together. Keep an observation identifier beside both variables and sort the entire record table.

Multiplying the original Y by ten and adding 7 gives 27,17,47,37, yet the expected coefficient remains 0.6. Negating Y instead gives -0.6. Scale changes and pair changes have different consequences.

Enter X -2,-1,0,1,2 in G2:G6 and Y 4,1,0,1,4 in H2:H6:

=PEARSON(H2:H6,G2:G6)

Expected result: 0. The means are 0 and 2. Centered products -4,1,0,-1,4 sum to zero; both centered sums of squares are positive.

Nevertheless, every Y value equals X squared. A scatter plot would show a clear curve. Zero Pearson correlation therefore does not demonstrate independence, and a high coefficient does not establish causation. The formula also supplies no significance level or confidence interval by itself.

COVAR gives 0.75 for the original four pairs, while COVARIANCE.S gives 1. Those measures retain input-unit effects; PEARSON normalizes them away.

With a constant input, the mathematical denominator is zero. The dedicated Google page reviewed does not name its native error result, so this guide does not certify a particular display. Empty arrays, single observations, unequal dimensions, mixed errors and extreme-value numerical stability remain outside the documented and independently modeled controls. Do not turn an undefined coefficient into a reported zero without a clear reporting policy.

• Google PEARSON reference.
• Editorial testing standards.

Examples use fictional data, English function names and comma argument separators. Expected results are independent calculations or explicitly documented rules, not native Sheets captures. Check spreadsheet locale when reproducing punctuation-sensitive formulas.