VARA Function (LibreOffice Calc)

VARA calculates the sample variance of observations, counting text values as zero. Variance quantifies how spread out observations are around their arithmetic mean; a larger result indicates more dispersion, measured in squared units. VARA divides the total squared deviations by n − 1, not n, where n counts the observations admitted by the function. This makes it a sample statistic, not a whole-population statistic. The formula is:

sample variance = SUM((each observation − sample mean)^2) / (count − 1)

Choose VARA only when treating a nonnumeric answer as zero is genuinely part of your data policy. For most numeric measurement datasets, VAR or VAR.S is safer because those functions ignore text inside a referenced range. Blank cells and text cells have importantly different effects in the native tests below. Official VARA syntax and purpose (LibreOffice 26.8 Help, checked again 2026-10-13; relevant section: VARA).

=VARA(Argument1 [; Argument2 [; ... [; Argument255]]])

The official syntax indicates up to 255 argument positions; you must supply at least two effective observations for a defined sample variance. There is no switch for cumulative, ignore_text, or population/sample mode. If your data includes one value only, the sample denominator is zero. The help uses semicolons (;) in formulas. Calc lets users switch to commas and choose localized or English function names in Tools → Options → LibreOffice Calc → Formula; the examples here use English names and Calc’s default argument separator. Official formula options (checked again 2026-10-13).

Worked example 1 — four numeric observations

Enter the following in Calc:

In another cell enter:

=VARA(A2:A5)

Expected and natively observed: 6.66666666666667 (display rounding depends on cell number format). The average is 5; squared deviations are 9, 1, 1, and 9; their sum is 20. Divide by 4 − 1 = 3 to obtain 20/3. The result is an independent arithmetic check as well as one of the saved native formula assertions. =VARA(2;4;6;8) gives the same result and was tested separately.

Worked example 2 — imported labels change the answer

Put 2, 4, the text n/a, and 8 in B2:B5, respectively. Leave no cell empty. Enter:

=VARA(B2:B5)

Natively observed: 11.6666666666667 (35/3). VARA treated the n/a text as zero, so the effective observations were 2, 4, 0, and 8, with a mean of 3.5. Their squared deviations total 35; the sample denominator is 3. By contrast, =VAR(B2:B5) uses the three numeric observations 2, 4, 8 and gives 9.33333333333333 in native Calc. This difference is why VARA should not be selected merely because a report contains occasional comments or N/A placeholders.

Worked example 3 — empty cells are not the same as text

Put 2 in D2, 4 in D3, leave D4 truly blank, and put 8 in D5. Do not type a space or a formula returning an empty string into D4. Then:

=VARA(D2:D5)

Natively observed: 9.33333333333333, because Calc used the three numeric observations and did not add a zero for a truly empty cell. This was separately observed as a diagnostic rather than a predeclared numerical assertion. Replace D4 with the literal word missing: on the observed Calc behavior, that text counts as zero and changes the variance. A cell that merely looks empty may actually contain a space or formula; use cell highlighting or examine the formula bar to distinguish it.

Worked example 4 — Boolean observations

Populate C2:C5 with numeric 2, numeric 4, the formula =TRUE() in C4, and numeric 8 in C5. Compute:

=VARA(C2:C5)

Natively observed: 9.58333333333333. In this LibreOffice test, TRUE() contributed as 1, giving [2, 4, 1, 8]; the sample variance is 115/12. Likewise, =VARA(TRUE();0;2) returned 1. If Boolean values represent status rather than measured quantities, converting them to 1/0 may be inappropriate for statistical interpretation. These Boolean outcomes are explicitly labelled native Calc 25.2 observations rather than blanket claims for all other spreadsheet apps.

1. Compare text-aware and numeric-only dispersion. With the B2:B5 example above:

=VARA(B2:B5)-VAR(B2:B5)

The independent arithmetic result is 7/3 ≈ 2.3333333333; it quantifies the effect of the n/a text-as-zero treatment in that particular sample. The exact subtraction expression was also natively observed, returning approximately 2.33333333333333.

2. Build a standard-deviation result. With A2:A5 numeric as above:

=SQRT(VARA(A2:A5))

Expected 2.58198889747161, the positive square root of 20/3. This equality was natively tested via =STDEVA(Examples.A2:A5)^2 and the corresponding square-root calculation; see the separately maintained STDEVA tutorial.

3. Combine separate ranges. For the same A2:A5 data:

=VARA(A2:A3;A4:A5)

Expected 6.66666666666667. The two references become one four-observation sample; do not average two independently computed sample variances unless you account for group sizes and group means. This expression passed native validation.

4. Distinguish sample variance from population variance. With all four A2:A5 values:

=VARA(A2:A5)
=VARPA(A2:A5)

The first is 20/3 ≈ 6.6666667 (native assertion). The second is expected 5 from the population formula 20/4, and the corresponding =VARPA(Examples.A2:A5) expression was also natively observed as 5. Use the VARPA guide for its own version-specific details.

5. Numeric-text trap. Set E2 to text '5 (stored as text, not number), E3=3 and E4=7:

=VARA(E2:E4)

Natively observed: 12.3333333333333. In this test, the numeric-looking text in E2 behaved like zero, so VARA used [0, 3, 7]. If E2 should mean five, convert that cell to a genuine numeric value before running the statistic. Do not assume VARA parses imported text as numbers just because characters look numeric.

6. Sample size and missing observations. A result from VARA is statistically meaningful only if the observations and their treatment are deliberate. Check the input range for placeholders and missing cells, and make a conscious choice about VARA vs VAR. COUNT counts numeric cells; COUNTA counts nonempty cells (including many text entries), so neither alone proves that every record is a valid statistical observation. Audit the data rather than relying solely on a cell-count threshold.

If some records are categories such as Pass/Fail, a zero may not represent either group meaningfully. Statistical conclusions should reflect the original units and missing-data policy. Extremely large, heavily rounded, or heterogeneously typed inputs also warrant cross-checking before publication; this batch did not stress-test maximum 255 arguments or floating-point stability on huge arrays. The 255-argument number is a documentation claim, not an executed boundary test.

These are different statistical operations. For people migrating older spreadsheets, verify formulas and results in the target application rather than changing suffixes by intuition.

LibreOffice Calc’s own help documents VARA and the observed Calc 25.2 behavior above. Microsoft documents VARA in Excel and Google documents VARA in Sheets, so the function name exists in all three, but formula separators, handling of booleans or numeric strings in particular argument forms, error displays, and import semantics can vary. We did not execute any Excel or Google Sheets formulas in this review. In exported spreadsheets, test representative cells containing text, blanks, Boolean formulas and imported numeric-looking strings in the destination application before assuming values match. Microsoft Excel VARA documentation and Google Sheets VARA documentation (checked again 2026-10-13; compatibility is documentation-checked only).

Native application: LibreOffice Calc 25.2.3.2 on Linux via UNO. In the saved validation package, vara_stdeva_native_tests.ods contains actual formula cells; vara_stdeva_native_results.json records formula strings, native results, numeric error codes and predeclared independent assertions. For VARA, 22 predeclared numeric comparisons passed and 18 additional edge cases were recorded separately as observations. Eight additional cross-check observations span this guide and STDEVA. The math checks used Python’s statistics.variance and ordinary square roots, independent of LibreOffice execution. Source references were checked again 2026-10-13, but the online 26.8 Help edition differs from the installed 25.2.3.2 used for observed behaviors.

Official and supporting references (checked again 2026-10-13 UTC)

• LibreOffice 26.8 Help, Statistical Functions Part Five, headings VARA, VAR, VARPA and STDEVA — primary syntax, sample/population distinctions, text treatment.
• LibreOffice 26.8 Help, Formula Options — English/localized names and separator configuration.
• Microsoft, VARA function for Excel — independent existence/compatibility reference, not execution proof.
• Google Docs Editors Help, VARA — independent Sheets function reference, not execution proof.