CORREL Function in Apple Numbers: Pearson Correlation with Paired Data

=CORREL(x-values, y-values)

No optional arguments or defaults are documented for CORREL. Apple requires both collections to have the same dimensions; its reference explicitly states that string and Boolean values in a collection are ignored. That does not, by itself, establish how arbitrarily mismatched blank/text pairs are aligned in every edge case, so the tutorial’s calculated examples use complete numeric pairs. Exact Apple CORREL reference.

Argument-order trap: Apple’s CORREL reference labels x first, y second. Its SLOPE and INTERCEPT references use y first, x second. Swapping fully numeric CORREL arrays does not change r mathematically, but always label your input ranges correctly.

These formulas are documented in Apple Formulas and Functions Help 15.4 (reviewed 2026-10-12). This is a documentation-version attribution, not a claim that the function was introduced in Numbers 15.4 or behaves identically on every older Mac, iPhone, iPad, or iCloud release. Apple Numbers native execution across platforms was not available for this review. Apple’s Numbers release documentation should be checked for availability in a particular installation.

The worked formulas are written for an English locale with a period as the decimal separator; arguments are separated with commas. Apple explicitly instructs that if the decimal separator is a comma, use a semicolon as the manually typed argument separator, for example a two-argument formula =FUNCTION(A2:A6;B2:B6) instead of =FUNCTION(A2:A6,B2:B6). The formula separator is not a data-range separator: A2:A6 still uses a colon. Function names are case-insensitive. See Apple’s Functions overview.

The simple ranges in the examples are in one Numbers table. Numbers supports table-qualified and sheet-qualified references when formulas refer to other tables or sheets; use Numbers’ cell-reference picker when working across tables instead of copying Excel’s Sheet!A2:A6 syntax. See Apple’s cell-reference guide.

1. Create a new Numbers table named Observations with at least six rows and four columns. Use row 1 as the header row. Enter X (hours) in A1 and Y (tasks) in B1. The observations are illustrative only; they are not evidence that hours cause task output.
2. Enter the following values as numeric cells, without typing hours or tasks into the number cells. Leave C2:C6 blank initially.
3. Enter the example formula in an unused cell such as D2, outside its two source ranges. Format the result cell to show enough decimal places if needed.

Every expected result below comes from independent calculation or Apple’s published worked example, not from execution in Apple Numbers.

First calculation: positive correlation

=CORREL(A2:A6,B2:B6)

Expected r = 0.7745966692414834 (approximately 0.7746). The means are x̄ = 3 and ȳ = 4. Sum of paired centered products is 6; sums of squares are 10 for x and 6 for y. Thus r = 6 / SQRT(10 × 6) = 0.774596669…. The Numbers display may round according to cell formatting. You can check the calculation independently using these figures without pretending it has been observed in Numbers.

1. Compare correlation with a fitted line

In the same table, put these formulas into three distinct unused result cells:

The fitted equation is predicted tasks = 0.6 × hours + 2.2. Its correlation coefficient and slope describe different things. A slope depends on measurement units, whereas correlation has no units.

2. Squared correlation as a simple-regression check

With one numeric predictor and a least-squares regression line that includes an intercept, r² is the fraction of variation explained by the fitted linear relationship for this sample. Here (0.7745966692)² ≈ 0.6. This is not a guarantee about prediction accuracy on new cases, proof of a causal mechanism or a general property of multivariable regression.

3. Negative association sanity check

For an independent second example, replace B2:B6 with the numbers 8, 6, 4, 2, 0 while leaving x = 1, 2, 3, 4, 5. The expected result of the same CORREL formula is −1, a perfect descending straight line. Restore the original B column to reproduce the first example.

4. Compare against Apple’s published example

Apple’s CORREL reference supplies ten price/thermostat-setting pairs and reports =CORREL(A2:A11,B2:B11) at approximately −0.907629573252938. Those are different data from this page’s five-row example. Enter all ten official pairs if you want to reproduce that result; changing only the formula range on this five-row table cannot reproduce it.

• Different-sized or differently shaped inputs: Apple requires matching collection dimensions, not merely matching totals or the same column letters. In the example, using A2:A5 with B2:B6 violates that rule. Align the full pairs first.
• Text/Booleans and absent observations: Apple says string and Boolean values are ignored in CORREL collections; do not interpret that as a licence to shift x and y rows independently. Keep numeric observations aligned by record. The handling of particular missing-value patterns needs native checking before offering a specific result or error message.
• All x values (or all y values) are identical: A standard Pearson coefficient divides by a standard deviation; if either variance is zero, the coefficient is mathematically undefined. The exact error shown by Numbers remains unverified.
• A very small dataset: With fewer than two useful distinct paired numeric observations, a meaningful sample correlation cannot be computed. Avoid extrapolating Apple’s undocumented exact error text from Excel.
• Near zero does not prove independence: It describes weak linear association, not necessarily the absence of a curved relationship.
• Formula displayed as literal text: Check that the entry was created as a formula beginning with = and that the destination cell isn’t formatted as plain text. The source cells should be numbers, not text like 3 hours.
• Unexpected value after copying tables: Ensure the references still point to paired source rows in the same intended Numbers table; watch for locale commas/semicolons.

• Microsoft Excel: Its official CORREL syntax is CORREL(array1,array2). For two fully numeric equal-sized ranges, it computes a Pearson correlation coefficient. Its error codes and rules for missing, blank, or mixed data may differ from Numbers; never use an Excel result to establish Numbers-specific edge cases.
• Google Sheets: Its CORREL help documents CORREL(data_y,data_x) with dependent data first, unlike Apple’s documented CORREL(x-values,y-values) label ordering. For the fully numeric paired example here, Pearson correlation is symmetric, so reversing the two entire series does not change r; the named argument roles do differ.
• Importing a file into Numbers can change table-qualified references. Review separators, source tables, data types, and error behavior after conversion.

• AVERAGE for means of the source series
• SLOPE for the fitted line rate of change
• INTERCEPT for the fitted y-axis crossing

Official Apple documentation, accessed 2026-10-12: function reference; Functions overview; table references; Numbers release documentation. The help URL specifies documentation version 15.4; no first-supported-release claim is made.

• DOCUMENTATION CHECK: Function purpose, names, argument order, allowed value types and documented limitations were transcribed from Apple, then paraphrased. There is no claim that Excel or Sheets documentation proves Numbers behavior.
• INDEPENDENT CALCULATION: Five-row worked examples and secondary computations were checked using transparent arithmetic (sum of centered products and sums of squared deviations). Expected results are not observed application outputs.
• APPLE PUBLISHED EXAMPLE: The heating-oil example is described as Apple’s example, with independently reproduced arithmetic; the quoted approximate output is not a native test performed here.
• NOT VALIDATED: Apple Numbers application execution, screenshots, regional-device testing and older-release behavior.

If a real Numbers installation disagrees, keep the discrepancy visible for investigation instead of hiding an error behind IFERROR.