COVARIANCE.P in Excel: Population Covariance and Unit-Sensitive Comparisons

• Introduced: the Microsoft function catalogue marks COVARIANCE.P as an Excel 2010 addition; an exact first build or preview date is not established here.
• Availability: official help lists Excel for Microsoft 365, Excel for Microsoft 365 for Mac, Excel 2024, Excel 2024 for Mac, Excel 2021, Excel 2021 for Mac, Excel 2019, Excel 2016.
• Checked: October 14, 2026; official COVARIANCE.P reference. Documentation and independently modeled examples; no native Excel execution or broader platform certification.

=COVARIANCE.P(array1, array2)

Both inputs are required. Use the population convention when the stated calculation describes the complete set of paired observations under study. Choosing .P instead of .S changes the denominator; it does not establish that a dataset is complete or representative.

The dedicated page’s argument labels say integers, while its general remarks say numbers. The worked examples use integers and do not infer an integer-only restriction from that inconsistent wording.

Enter X values 1, 2, 3 and 4 in A2:A5 and paired Y values 2, 1, 4 and 3 in B2:B5.

=COVARIANCE.P(A2:A5,B2:B5)

Expected result: 0.75. Both means are 2.5. The paired products of deviations are 0.75, 0.75, 0.75 and 0.75, summing to 3. Divide by four pairs: 3/4=0.75.

This agrees with Microsoft’s displayed equation, which divides the centered-product sum by n. COVARIANCE.S instead gives 1 for the same four pairs.

In D2:E4 enter the pairs (0,0), (1,2), (2,4).

=COVARIANCE.P(D2:D4,E2:E4)

Expected result: 4/3, approximately 1.333333333. The means are 1 and 2; centered products are 2,0,2.

If the zero pair is deliberately removed, the remaining two pairs have population covariance 0.5. Zero is a measurement, not an empty cell. Dropping it changes both means and the denominator.

For the first dataset, enter =B2*10 in C2 and fill down. The transformed Y values are 20,10,40,30.

=COVARIANCE.P(A2:A5,C2:C5)

Expected result: 7.5, ten times the original. If Y is expressed in a unit ten times as numerous, the covariance scales with it. Adding a constant 7 to each original Y value leaves covariance at 0.75, because deviations from the mean do not change.

Multiplying X by 2 and Y by 3 gives covariance 4.5, six times the original. Multiplying only Y by -1 gives -0.75. Compare CORREL when a unitless linear-association coefficient is the desired quantity.

Divide the original four pairs into rows 2–3 and rows 4–5. Each two-pair subgroup has population covariance -0.25. Their ordinary average is therefore -0.25, but the full dataset covariance is +0.75.

The subgroup means differ: the first group is centered on (1.5,1.5), and the second on (3.5,3.5). A full calculation uses the overall center (2.5,2.5), capturing the between-group movement too. Do not average subgroup covariances as a shortcut to the covariance of the combined records.

As a separate consistency check, duplicating the entire four-pair dataset gives eight pairs with centered-product sum 6. Population covariance stays 6/8=0.75. Duplication changes the count but not this empirical distribution.

Microsoft documents #N/A for unequal data-point counts and #DIV/0! for an empty input. Text, logical and empty reference cells are ignored, but zeros are included. The exact handling of independently placed missing cells is not established by these all-numeric paired controls.

A constant X column paired with varying Y has centered products all zero and modeled covariance 0, unlike correlation’s undefined zero-variance denominator. This guide does not assign undocumented native errors to every singleton or mixed-type case. Preserve row identities and inspect the paired sample before calculating.

• Microsoft COVARIANCE.P reference.
• Editorial testing standards.

Expected results are independent statistical/arithmetic models or explicitly documented rules, not captured native output. Examples use English function names and comma separators. Datasets and reporting policies are fictional.