VARA in Google Sheets: Sample Variance with Explicit Text-to-Zero Inputs

• 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 VARA reference. Documentation and independent models, with no native Google Sheets execution or wider client certification.

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

The first value or range is required. Additional values or ranges are optional. Google documents support beyond the nominal 30 arguments, without establishing a tested practical maximum in this guide.

Choose the sample according to the question. Variance has squared input units; it is not an average duration or a standard deviation. The calculation cannot decide whether text really should represent zero in your data.

Put numeric 12 in A2, numeric 18 in A3 and ="pending" in A4.

=VARA(A2:A4)

The documented text-zero model uses 12,18,0, whose mean is 10. The squared deviations are 4,64,100 and sum to 168. There are three encoded observations, so the sample denominator is two.

Expected result: 84. If the numbers represent minutes, the variance is 84 minutes squared. Its square root is approximately 9.165151390 minutes, which has the original units.

The word pending may actually indicate a missing observation rather than a measured zero. The result is only meaningful if the stated encoding is appropriate for the report.

=VARA(12,18)

Expected result: 18. The mean is 15, the squared deviations total 18 and the denominator is one.

The difference between 18 and 84 is caused by adding the zero-valued observation. A label is not ignored merely because it cannot be read as a number. Conversely, formatting numeric 12 as text is not a harmless change when a function has an explicit text policy; numeric-text edge cases need their own test.

For A2:A4 above, fill this helper from B2 through B4:

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

The values become 12,18,0. Numeric-only checks are:

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

Both have expected result 84. The helper lets the reader inspect the encoding rather than infer it from a statistical output.

COUNT of the original mixed range is 2, while COUNT of the helper is 3. Using the original numeric count in a hand-built sample denominator would be wrong. This helper is scoped to ordinary text and numbers, with empty cells, Booleans and errors excluded from the exercise.

For the numeric encoded list 12,18,0, sample variance is 84. Repeat that entire list to get 12,18,0,12,18,0.

=VARA(12,18,0,12,18,0)

The mean remains 10, the squared-deviation total doubles to 336, and the sample denominator becomes 5. Expected variance: 67.2.

Relative frequencies are unchanged, but the n−1 adjustment changes. An accidental duplicate import should not be interpreted as additional independent evidence simply because the sample statistic accepts the rows.

=VARA(112,118,100)
=VARA(120,180,0)

Expected results are 84 and 8400. Adding 100 to every encoded observation leaves deviations unchanged. Multiplying every observation by ten multiplies variance by one hundred.

These transformations apply to the explicit numeric list. Adding 100 to the two measured cells while leaving the text label unchanged would not shift the whole encoded sample: the label would still be zero. Make transformations visible after the input policy is settled.

On the same encoded 12,18,0 data, VARPA gives population variance 56. Multiplying 56 by 3/2 recovers sample variance 84. Both results can be correct under different questions, provided they use the same observations.

Google explicitly documents #DIV/0! when fewer than two values are supplied. A numeric =VARA(5) therefore has that documentation-based error expectation. =VARA(5,5) instead gives 0, because two equal accepted values have no spread.

The source says every encountered text value is set to zero. It also points to VAR for an error on text, while VAR’s own page uses narrower all-text wording. This guide does not turn those different descriptions into a complete cross-function type table.

Primary text controls use the ordinary label pending. Numeric text, formula-empty strings, actual blank cells, logical values and mixed errors remain unverified. Resolve those inputs before relying on a denominator or applying a blanket error replacement.

Use VAR.S for a separately validated numeric sample. Keep the original text and the chosen numeric encoding together when the result is shared.

• Google VARA 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.