COVARIANCE.P

What it does

COVARIANCE.P(array1, array2)

COVARIANCE.P(array1, array2) measures population covariance: whether paired observations tend to move in the same direction. It averages products of deviations using divisor n. Covariance carries the product of the two input units.

Try it

=COVARIANCE.P({1,2,3},{2,4,6}) returns approximately 1.33333333333333. The means are two and four; the paired deviation products are two, zero and two.

With an excluded position, =COVARIANCE.P({1,"missing",3},{2,4,6}) returns 2 under the current paired collection rule. The second position is skipped on both sides; the remaining pairs are (1,2) and (3,6).

Quirks

The original arguments need the same number of cells; otherwise the current collector returns #N/A. It flattens them in traversal order and preserves positional alignment. If either side of a position is text, logical or blank, that position supplies no pair. Numeric zero still participates. Worksheet errors are read and propagated rather than silently removed with a nonnumeric partner.

An empty set of eligible pairs gives #DIV/0!; one pair has zero population covariance. Do not filter each side independently and zip the remaining lists: that can pair observations that never belonged together.

How to implement

The current paired collector checks equal original cell counts, then walks both arguments together and retains only positions where both values are numeric. It checks the first argument’s value before the second’s at each position. This admission step belongs before either mean is calculated.

1. Let n be the number of retained pairs. Apply the empty-set and sample-count checks above.
2. Accumulate separate binary64 sums Sx and Sy, then store mx=Sx/n and my=Sy/n.
3. Starting from C=0, visit each retained pair and add the ordinary binary64 product (x-mx)*(y-my).
4. Return C/n. The current helper uses ordinary binary64 operations here; no stored-stage identification for the whole Excel route is claimed.

For the excluded-position example, the retained means are two and four. Products (-1)*(-2) and 1*2 total four. Divide by two to obtain population covariance two. The count is two, not the three original positions.

Covariance retains both input scales: multiplying every y by ten multiplies the mathematical covariance by ten. CORREL normalizes that scale away. The exact-arithmetic relation does not certify the last bits of either implementation.

Rust code

36 covariance_p "COVARIANCE.P" (2, 2) [R "array1", R "array2"] |s, a, sv| {
    out(s, paired::covariance_from_pairs(&pairs(s, a, 0, 1, PairScalars::SkipNonnumeric, sv)?, CovarianceDivisor::Population))
}

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

Version differences

No same-input comparison between named Excel builds is recorded for this function here. The documented behaviour below is not a measurement of every Excel release.

Alternatives

CORREL removes units and scales to a correlation coefficient. COVARIANCE.S changes the denominator.

Named variants

No named variants are recorded yet.