CORREL

What it does

CORREL(array1, array2)

CORREL(array1, array2) returns the Pearson product-moment correlation coefficient of two paired data sets.

For pairs (x₁,y₁), …, (xₙ,yₙ) with means x̄ and ȳ,

r  =  Σ (xᵢ − x̄)(yᵢ − ȳ)  /  √( Σ (xᵢ − x̄)² · Σ (yᵢ − ȳ)² )

equivalently r = cov(x,y) / (σ_x σ_y), and equivalently again the cosine of the angle between the two mean-centred data vectors. Microsoft's page states the same formula with x̄ and ȳ given as AVERAGE(array1) and AVERAGE(array2).

Note that the n versus n − 1 question — population or sample divisor — does not arise. The same divisor appears in the numerator and in both factors of the denominator and cancels identically. CORREL is therefore the one member of this cluster with no population/sample split: there is no CORREL.P and there could not be one.

Range and interpretation. By Cauchy–Schwarz, r ∈ [−1, 1] exactly, with |r| = 1 if and only if the points lie on a straight line. r = +1 is a perfectly increasing line, r = −1 a perfectly decreasing one, r = 0 no linear association. The last qualifier is the one that costs people money: r measures linear association only, and a perfect parabola through symmetric x values has r = 0.

Invariance. r is unchanged by any increasing affine transformation of either variable — r(ax+b, cy+d) = r(x,y) for a, c > 0 — and flips sign when exactly one of a, c is negative. This scale invariance is what distinguishes r from COVARIANCE.P, whose value carries the units of x times the units of y and is therefore uncomparable across data sets.

Degenerate cases. r is undefined when either variable has zero spread — the denominator vanishes because every point of that variable sits on its own mean — and undefined when fewer than two pairs survive. Both surface as errors rather than as values; see below.

Arguments

The documented filtering rule is the important one: text, logical values and empty cells inside an array or reference argument are ignored, and cells containing zero are included. This is a range-scan policy, not a coercion policy — the text "3" in a cell does not become the number 3 here, it is skipped. See Coercion and lifting for why the same value behaves differently as a direct argument than as a scanned cell.

Try it

Quirks

Result and edge cases

Returns Number in [−1, 1].

Documented behaviour:

• Different numbers of data points → #N/A. Not #VALUE!. This is the length check, and it is performed on the raw extents, before the ignore-rule has removed anything.
• Either array empty, or a standard deviation of zero → #DIV/0!. A constant column is not a correlation of zero; it is an error.
• Text, logicals and blanks inside the ranges are ignored; zeros are included.

Behaviour the documentation does not settle:

Errors in a scanned cell propagate: an error value inside either range becomes the result, which is consistent with the general rule that coercion never silently discards a worksheet error (chapter 02).

How to implement

Numerical notes

Correlation is the classic worked example in the numerical-statistics literature, and for a good reason: the textbook formula is a subtraction of two nearly equal large numbers.

The unstable form. Expanding the centred sums gives

Σ(xᵢ−x̄)(yᵢ−ȳ) = Σxᵢyᵢ − (Σxᵢ)(Σyᵢ)/n

which is attractive because it accumulates in one pass with five running sums. It is also catastrophic when the data have a large mean relative to their spread: both terms grow like n·x̄·ȳ while their difference is only n·cov, so the relative error is amplified by roughly |x̄ȳ| / |cov|. On data like (10⁸ + small) the correlation can come out as 0, as 2, or as #DIV/0! from a negative sum of squares. Chan, Golub & LeVeque (1983) is the standard analysis of exactly this failure, and the Gnumeric/Welinder tradition of auditing spreadsheet statistics grew out of finding this formula in shipped products.

Overflow. With the centred two-pass form, Σ(xᵢ−x̄)² can still overflow for data near the double-precision ceiling. Scaling by the maximum absolute deviation before accumulating — the same trick used in a careful hypot — is the standard defence and costs one extra pass.

Range guarantee. Mathematically |r| ≤ 1; numerically, the ratio of independently rounded quantities is not guaranteed to satisfy it. An implementation that intends to promise the bound has to clamp explicitly, and clamping is a decision to record rather than a neutral cleanup: it changes what ACOS(CORREL(...)) does at the boundary.

None of this is a claim about how Excel computes CORREL. It is the map of where the errors live for anyone measuring it.

Rust code

38 correl "CORREL" (2, 2) [R "array1", R "array2"] |s, a, sv| {
    out(s, paired::correlation_from_pairs(&pairs(s, a, 0, 1, PairScalars::SkipNonnumeric, sv)?))
}

No implementation has been published with a Handbook test suite for this function.

Version differences

No comparison between specific Excel builds is recorded here yet. This does not establish that all builds behave alike.

Alternatives

Relationships

• PEARSON — documented as computing the same coefficient. Excel ships two spellings of one statistic. Neither is deprecated, neither is in the Compatibility category, and the Handbook has not checked whether they return identical bits; a shared definition is not a shared implementation. Comparing them is one of the cheapest interesting probes in the whole statistical lane.
• COVARIANCE.P / COVARIANCE.S — the unnormalized cousins. CORREL is covariance divided by the product of the standard deviations, and the population/sample choice cancels out of that ratio.
• FISHER — the variance-stabilizing transform artanh(r) used to build confidence intervals for a correlation. It is the natural next function after this one, and its own page is where the r → ±1 sensitivity gets its proper treatment.
• Confused with: causation, and with SLOPE. r is unitless and symmetric in its arguments; SLOPE is neither.

Named variants

No named variants are recorded yet.