COVAR in Google Sheets: Paired Population Covariance and Unit Changes

• Introduced: first introduction date is not established by the reviewed official history; no desktop release number is inferred.
• Availability: documented in the rolling Google Sheets service; this guide uses the web worksheet workflow.
• Checked: October 14, 2026; official COVAR reference. Documentation and independent calculations, with no native Sheets execution or wider client certification.

=COVAR(data_y,data_x)

Both ranges are required. Google labels the first series dependent data and the second independent data. Supply one aligned observation from each series per row; this naming does not establish a causal relationship.

The official function catalogue directs COVARIANCE.P to COVAR. This guide therefore uses the population convention: divide the sum of paired centered products by the number of pairs. That documented naming relationship does not mean that a similarly named sample function uses the same denominator.

Google says text is ignored, but the concise page does not fully specify mismatched missing cells, Boolean conversion or all error outputs. All worked pairs here are numbers, and no independent filtering of the two columns is performed.

Enter this table in A1:B5:

=COVAR(B2:B5,A2:A5)

Expected result: 0.75. Both means are 2.5. X deviations are -1.5,-0.5,0.5,1.5; Y deviations are -0.5,-1.5,1.5,0.5. Each paired product is 0.75, giving total 3. The population denominator is four.

To inspect this directly, put =AVERAGE(A2:A5) in E2 and =AVERAGE(B2:B5) in E3. In C2 enter =(A2-$E$2)*(B2-$E$3) and fill down. =SUM(C2:C5)/4 also gives 0.75 in the independent calculation.

The fixed denominator is justified by this complete four-pair fixture. Do not reuse it after the included records change.

Multiply each Y value by ten in D2:D5, producing 20,10,40,30:

=COVAR(D2:D5,A2:A5)

Expected result: 7.5. The centered Y deviations and all paired products are ten times larger. The linear relationship has not become ten times stronger merely because the unit scale changed.

Adding 7 to every original Y value instead gives 9,8,11,10 and leaves covariance at 0.75. Negating Y gives expected -0.75, reversing the direction. Multiplying X by 2 and Y by 3 gives 4.5, six times the original.

The first two original pairs have means 1.5 and 1.5, with covariance -0.25. The last two have means 3.5 and 3.5, again with covariance -0.25.

=COVAR(B2:B3,A2:A3)
=COVAR(B4:B5,A4:A5)

Yet the combined four-pair covariance is positive 0.75. The separation between group centers contributes to the pooled result. Averaging subgroup covariances would lose that information and return the wrong description of the combined records.

Keep group membership available when interpreting a pooled statistic. A positive overall result can coexist with a negative association within each group.

For pairs (0,0), (1,2), (2,4), the means are 1 and 2. Centered products 2,0,2 total 4, so expected population covariance is 4/3, approximately 1.333333. Removing the zero pair leaves covariance 0.5. A measured zero is not equivalent to an excluded record.

Duplicating all four original pairs preserves population covariance 0.75: the numerator doubles from 3 to 6 and the count doubles from four to eight. Duplication still changes counts and provenance, even when this statistic stays fixed.

COVARIANCE.S divides the original numerator by three and gives 1. Choose the sample or population interpretation from the reporting question, rather than whichever result looks preferable.

PEARSON gives 0.6 for the original pairs and provides a dimensionless linear coefficient. Neither measure proves causation. Empty arrays, unequal shapes, mixed errors and constant-series native error behavior are not certified by this guide; inspect complete paired numeric data before applying a fallback.

• Google COVAR reference.
• Editorial testing standards.

Examples use fictional data, English function names and comma argument separators. Expected results are independent calculations or explicitly documented rules, not native Sheets captures. Check spreadsheet locale when reproducing punctuation-sensitive formulas.