PEARSON Function in Apple Numbers: Correlation Formula and Examples

=PEARSON(x-values, y-values)

There is no optional method, sample/population, intercept or rounding argument, and no documented defaults. Apple’s signature names the x collection first, then y. Although swapping the two complete collections leaves a correlation coefficient unchanged mathematically, it is important that pairs remain aligned. Sort both columns together rather than sorting either alone. The Apple reference does not establish exactly how every missing value, text cell or Boolean is paired or coerced in every release; this guide’s reproducible cases contain only numeric observations and no blanks. [A1, C1]

For numeric pairs (xᵢ,yᵢ), Pearson’s coefficient is the product of each centered deviation summed over observations, divided by the square root of the product of the two centered squared-deviation sums. It is dimensionless. Multiplying all values of one variable by a positive constant does not change the coefficient; changing the sign reverses it. This mathematical explanation is an independent derivation, not a claim about undocumented Numbers coercions. [C1]

These examples target the current English Apple Formulas and Functions Help, edition 15.4. In Numbers, make a named table, type numeric observations into cells, select the formula result cell outside its inputs, and use the formula editor to select the source cells. A range such as B2:B6 is a reference inside the same table. For data in a different table, select that table’s cells and let Numbers insert a qualified reference such as Measurements::B2:B6. Avoid Excel’s Sheet1!B2:B6: its exclamation-mark sheet notation is not Numbers’ reference convention. Apple explains table references in Refer to cells in formulas. [R2]

Apple’s English function syntax uses commas to separate arguments, for example =AVERAGE(2,4). When the document’s settings use a comma as the decimal separator, Apple’s Functions overview tells users to separate manually typed arguments with a semicolon—for example =AVERAGE(2;4). A range’s colon remains a colon. Enter values with the decimal symbol configured for the file. The spellings shown below are the canonical English function names; Apple’s overview says capitalization does not affect manually entered names. [R1]

Apple documents these functions in Formulas and Functions Help 15.4 and makes its formulas help available to iWork apps. That help edition does not prove the function first appeared in Numbers 15.4 or establish a minimum release for Mac, iPhone, iPad or iCloud.com. Apple’s Mac release-history page separately identifies Numbers 14.4 as adding over 30 advanced functions such as LET, LAMBDA and FILTER; it does not specify a PEARSON/RSQ/STANDARDIZE introduction date. Release-specific behaviour, display formatting and parity across Mac, iPhone, iPad and iCloud.com are not natively tested here. [R3, R4]

Verification key: “Apple-documented” means a statement appears in the cited Apple Help article. “Independently calculated” means the arithmetic was reproduced without the Numbers engine (exact rational or standard-library mathematics). Neither is an on-device formula execution. No screenshots or instrumented Numbers execution is claimed.

Apple’s example uses no header row and supplies five numeric pairs. To reproduce Apple’s exact A1:A5 reference, make a separate Numbers table and switch off its header row or place the data in the first row. If you use a header row instead, shift every row/reference one position and use B2:B6/C2:C6 accordingly. [A1]

=PEARSON(A1:A5,B1:B5)

Apple-documented result: 0.457010766448682. The independently calculated coefficient is approximately 0.4570107664486824; the discrepancy beyond the quoted final decimal is just rounding. The sign is positive, but the relationship is not a perfect straight line. Apple’s documentation also states that CORREL produces the same coefficient for the same data; it is not a separate second correlation statistic. [A1, C1]

Independent arithmetic audit for that five-pair example

With n=5, the independent sums are Σx=23, Σy=34, Σxy=173, Σx²=159, Σy²=256. The centered numerator in the no-fractions form is 5×173−23×34=83; the two denominator components are 5×159−23²=266 and 5×256−34²=124. Thus r = 83/√(266×124) ≈ 0.4570107664486824. The coefficient of determination, r² = 6889/32984 ≈ 0.208858840650012, is the quantity returned by RSQ for this table. These calculations were checked independently; they were not executed in Numbers. [C1]

The second through fourth outcomes include independent numerical checks; the in-app behaviour and any column-label displays remain untested. [A1, A2, C1]

Make a separate table called Journeys with Journey in A1, Distance km in B1 and Time min in C1. Enter these exact observations.

=PEARSON(B2:B5,C2:C5)

Independently calculated expected result: 1. The four pairs lie exactly on Time=2×Distance. Reverse the recorded times to 8,6,4,2 without altering distances and the independent expected result becomes −1. These are useful sanity checks: the function measures linear direction and strength, not slope or elapsed minutes. For real journeys, a strong relationship should not be mistaken for proof that distance alone causes delay. [C2]

More realistic imperfect relationship

Change the four times in C2:C5 to 2,5,4,8. The independent expected coefficient is approximately 0.877876225140348, while =RSQ(B2:B5,C2:C5) is 289/375 ≈ 0.770666666667. Using RSQ alongside PEARSON makes sense only when interpreting a simple straight-line model; RSQ drops the sign of the association. [C2]

• Mismatched collection lengths: Apple explicitly requires both collections to have the same count. Check row endpoints and verify that a filtered or edited dataset hasn’t shifted one side of a pair. Do not invent an exact error code without testing the installed app. [A1]
• Constant values in one column: Pearson’s mathematical denominator becomes zero, so the coefficient is undefined. For example, x=1,1,1,1 cannot quantify a correlation. Apple’s page does not specify the exact display/error in this case; validate natively. [C1]
• Fewer than two distinct pairs: There is not enough dispersion to define a meaningful product-moment correlation; confirm source data and don’t interpret an error as a zero correlation. This is a mathematical limit, not a verified Numbers message. [C1]
• Blanks, text and dates: Apple’s PEARSON description names collections and equal sizes but does not state how these inputs are paired, dropped or converted. Avoid silently accepting them. Clean the dataset into two fully numeric, row-aligned columns and test if edge-case support matters. [A1]
• Wrong sign: Verify each row represents the same observation in both columns. Sorting the two columns separately destroys the pairing even if each column still looks plausible. [C1]
• Unexpected magnitude: Correlation is unitless. Formatting the result as a percentage can obscure its signed −1 to +1 interpretation. Do not mistake 0.46 for “46% of observations explained.” RSQ provides the squared coefficient for a single-predictor linear fit, subject to modeling assumptions. [A2, C1]
• Curved or time-dependent relationships: Pearson can be near zero when a strong nonlinear pattern exists, and trends can create spurious correlation. Inspect a scatter chart and the domain context, not only this number. [C1]
• Circular references: Put the output formula outside the two source ranges. Use the formula editor and qualified table names for cross-table links. [R2]

Several spreadsheet products offer a function named PEARSON and may compute the same mathematical coefficient from clean numeric pairs, but their handling of blanks, text, non-contiguous ranges, error values, localization and import/export must be researched separately. This Numbers guide asserts only Apple’s documented signature and separately computed clean-data examples; another product’s edge-case rules are not Numbers evidence. When exchanging files, use Numbers’ native references rather than transplanting an Excel-style Sheet1!A1:A5 expression. [A1, R2]

• CORREL — an alternative coefficient name Apple explicitly equates with PEARSON for the same pairs.
• RSQ — squares Pearson’s coefficient to describe simple straight-line fit, but loses sign.
• COVAR — unstandardized population covariance, unlike dimensionless Pearson r.
• SLOPE — measures the change in y per unit change in x, rather than strength alone.

• [A1] Official function definition, signature, argument descriptions and example: Apple PEARSON, Functions Help 15.4.
• [A2] Related mathematical relationship and example: Apple RSQ, Functions Help 15.4.
• [R1] Function-name casing, required/optional notation and separator rules: Apple Functions overview.
• [R2] Native table references: Apple Refer to cells in formulas.
• [R3] Help and app scope: Apple Formulas and Functions Help.
• [R4] Version-specific release documentation: Apple What’s new in Numbers on Mac.
• [C1] Exact rational sums and coefficient reproduced independently using Python’s standard numeric operators on the five published pairs, 2026-10-13; no Numbers engine.
• [C2] Four-pair perfect/negative/imperfect cases computed separately with the Pearson equation, 2026-10-13; no Numbers engine.

Native-validation limitations: No Numbers execution or screenshots were performed. Check important mixed-type, blank and locale edge cases on the intended device.