VARPA in Google Sheets: Population Variance and the Cost of Text Zeros

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

=VARPA(value1,[value2,...])

Supply a first value or range and optional additional values or ranges. Google states that Sheets supports more than the nominal 30 arguments; a native maximum-size limit was not tested here.

The function sums squared deviations from the included-data mean and divides by n. Choose the complete population deliberately. The same observed records may instead be a sample when the question concerns a wider process or future period.

Enter numeric 12, numeric 18 and formula ="pending" in A2:A4.

=VARPA(A2:A4)

Google’s text-zero rule gives the encoded population 12,18,0. Its mean is 10 and squared deviations are 4,64,100, totaling 168. Divide by three for expected variance 56.

The square root is approximately 7.483314774, the corresponding population standard deviation. If the original measurements are minutes, variance is in minutes squared, while that standard deviation is in minutes.

The formula does not establish that pending means a measured zero. Preserve the status label and decide whether the unresolved record should be part of this population before interpreting the calculation.

=VARPA(12,18)

Expected result: 9. The two measurements have mean 15 and squared-deviation total 18, divided by two.

Thus the first result of 56 describes a materially different population. A worksheet may look like it still contains only two numbers, but a text-inclusive function has a third encoded value. A valid arithmetic result can still answer the wrong reporting question.

For A2:A4, fill this formula from B2 to B4:

=IF(ISTEXT(A2),0,A2)

The numeric helper is 12,18,0. These two checks agree:

=VAR.P(B2:B4)
=STDEV.P(B2:B4)^2

Expected result: 56. COUNT of B2:B4 is 3, while COUNT of the original A2:A4 is 2. A manual population denominator based only on the original numeric cells would not match the intended encoded calculation.

This helper is only scoped to the supplied numbers and ordinary text. It does not certify actual blank cells, logical values or source-error behavior.

Repeat the encoded population to obtain 12,18,0,12,18,0. The mean stays 10, the squared-deviation sum doubles to 336 and the count doubles to six. Population variance remains 56.

By contrast, adding one more text label creates encoded 12,18,0,0. Its mean is 7.5, squared-deviation sum 243, and population variance 60.75. The relative weights have changed.

A reporting template that fills unused rows with text can therefore affect VARPA if the selected range expands into those rows. Empty-cell behavior is a separate question; this example deliberately uses actual text labels.

=VARPA(120,180,0)
=VARPA(-24,-36,0)
=VARPA(112,118,100)

Expected results are 5600, 224 and 56. The first multiplies all encoded inputs by 10 and variance by 100. The second multiplies them by −2 and variance by four. The third adds 100 to every encoded number and leaves variance unchanged.

These are transformations of the explicit numeric population. Changing only measured cells while leaving text placeholders unchanged is not equivalent to transforming every observation.

For the same encoded data 12,18,0, VARA gives sample variance 84. Multiplying by (3−1)/3 gives population variance 56. Keep both the data and its inclusion rule identical when applying that relationship.

Google’s VARPA page explicitly names #NUM! when fewer than two values are supplied. Thus numeric =VARPA(5) has that documentation-based expectation here. The related VARP page names #DIV/0! for its minimum-data case; this guide does not silently replace VARPA’s published error with the neighboring function’s code. Neither was executed natively for this article.

=VARPA(5,5) instead has expected variance 0. Two equal observations are different from an insufficient input.

The VARPA page recommends VARP for an error on text, but VARP’s own page says text arguments are ignored. That cross-reference conflict remains explicit; it is not evidence for a universal alternative-function error rule.

The ordinary pending-label controls follow VARPA’s own text-zero definition. Numeric text, formula-empty text, Booleans, actual empty cells and mixed errors need separate verification. STDEVPA provides the square-root measure for the same accepted population, with its own documented minimum-data error wording.

• Google VARPA reference.
• Editorial testing standards.

Examples use fictional inputs, English function names and comma argument separators. Expected outputs are independently modeled from the cited rules, not native Sheets captures. Check the spreadsheet locale when reproducing punctuation-sensitive formulas.