VARPA Function (LibreOffice Calc)

VARPA measures the variance of an entire population, including a contribution of zero from text observations. Variance describes how far the data points spread around their mean: the higher the result, the more spread out the data are. Because it is a population statistic, the denominator is the number n of observations it counts, not n − 1 as in sample variance.

population variance = Σ(each counted observation − population mean)^2 / n

The result is measured in squared units. If you have a sample intended to estimate a larger population’s variance, see VARA (sample variance that counts text as zero), or use the numeric-only sample functions as your data policy requires. If text indicates a missing measurement rather than an actual numeric zero, do not use VARPA without first cleaning the data: silently including it as zero changes the denominator, mean and variance. LibreOffice 26.8 Help: VARPA, VARP, VARA (checked again 2026-10-13).

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

The square brackets above describe optional portions of the documented signature—they are not characters to type. In an actual formula, write for example =VARPA(A2:A5) or =VARPA(2;4;6;8).

The current official signature lists up to 255 argument positions, not a limit of 255 cells inside one range. There is no separate flag for population/sample mode, text conversion or cumulative output; no default value is supplied for a missing Argument1. Text is interpreted as zero under this function’s own documented rules, while genuinely empty cells were ignored in native Calc tests. The observed result for one valid number was zero; there is no division-by-n-1 in this function.

Locale and formula entry. These examples use English function names and semicolons between arguments, as shown in official Calc Help. In Tools → Options → LibreOffice Calc → Formula, function names can be localized and the argument separator can be switched to comma. A locale using a comma decimal separator may display fractional numbers differently: use your locale’s numeric entry rules rather than blindly pasting dot-decimal constants. The sheet-reference form in this guide is Calc A1 syntax (Data.A2:A5); other formula-syntax preferences alter cross-sheet notation. LibreOffice Formula Options (checked again 2026-10-13).

Create a sheet named Data. Enter values (not formulas, except TRUE and FALSE as indicated) into the following ranges. Blank means leave the cell truly empty—do not type a space or ="".

If you use an unprefixed formula on the same Data sheet, omit Data. below. The native test workbook preserved in the saved validation package uses exactly these ranges.

Example 1: four numeric measurements

=VARPA(Data.A2:A5)

Native Calc result: 5. Independent calculation: 5. The mean of [2, 4, 6, 8] is 5. Squared deviations are [9, 1, 1, 9], totaling 20; population variance is 20 / 4 = 5. The separate literal formula =VARPA(2;4;6;8) was also natively executed and returned 5.

Example 2: a n/a label does not mean missing

=VARPA(Data.B2:B5)

Native Calc result: 8.75. Independently calculated: 8.75. The observed Calc behavior is as if you had provided [2, 4, 0, 8]. Their mean is 3.5, and the total squared deviations are 35; dividing by four gives 8.75. Contrast the numeric-only VARP function, whose documented behavior ignores text in a range. Do not remove a n/a marker merely by formatting its cell as a number; its stored content matters.

Example 3: a genuinely blank cell is omitted

=VARPA(Data.C2:C5)

Native Calc result: 6.22222222222222, the independently calculated 56/9. The actual population was [2, 4, 8], with mean 14/3. A blank cell was not added as a fourth zero. This is an important difference from the n/a example. Explicit =VARPA(2;4;8) reproduced the same result. In separate diagnostics, a cell containing a formula whose result is an empty string ="" did behave like a text-as-zero observation in Calc 25.2.3.2. That distinction is version-observed, not a guarantee for another application.

Example 4: logical results and imported numeric-looking strings

For the D range with =TRUE() and =FALSE() in D4 and D5:

=VARPA(Data.D2:D5)

Native result: 2.1875. The tested Calc version treated the logical values as 1 and 0, producing effective [2, 4, 1, 0]. Their mean is 1.75; population squared deviations sum to 8.75, so divide by four to get 2.1875. Status flags are generally not measurements, so a text-aware population variance is rarely an appropriate analysis of mixed statuses and quantities.

For the E range, E4 is a number-looking text value ('7, stored as text):

=VARPA(Data.E2:E5)

Native result: 8.75, exactly like the n/a example. Even though E4 visually resembles a number, the observed Calc test treated it as text=0. To incorporate the numeric 7 instead, convert it to an actual number using a deliberate data-cleaning step, then verify its number format/value before calculation.

Audit text contamination before computing variance

For a column intended to contain only numeric measurements, detect unexpected filled nonnumeric entries first:

=COUNT(Data.B2:B5)
=COUNTA(Data.B2:B5)
=IF(COUNT(Data.B2:B5)=COUNTA(Data.B2:B5);VARPA(Data.B2:B5);"Review nonnumeric entries")

For the B range, the expected first two results are 3 and 4, so the final formula is expected to display Review nonnumeric entries. These three guard-formulas were reasoned from documented functions; they were not part of the 52 native VARPA/STDEVPA assertions. Do not assume this guard rejects Boolean formula results; separately validate status flags. It does not replace full input-quality rules (for example, a value that looks empty because of custom formatting).

Compare two populations or two policies carefully

=VARPA(Data.B2:B5)-VARPA(Data.C2:C5)

Independent expected result: 8.75 − 56/9 = 2.52777777777778. This exact subtraction was not natively executed. The difference comes partly from counting a text observation as zero in B but omitting a genuinely blank observation in C. Don’t interpret that difference as a true change in process variability without ensuring comparable populations.

Combine noncontiguous observations

=VARPA(Data.A2:A4;8)

Independently expected result: 5. The saved native test evaluated the difference between this expression and VARPA(Data.A2:A5) and returned zero. Each range or scalar argument joins the same population. Avoid averaging variances of separate groups without appropriate population sizes and means: the pooled variance is not generally the unweighted average of group variances.

Link variance and standard deviation

=STDEVPA(Data.B2:B5)^2
=VARPA(Data.B2:B5)-STDEVPA(Data.B2:B5)^2

For the B range, the expected first result is 8.75, and the second was natively executed and returned 0 within the application’s floating-point precision. On the same input, population standard deviation is the square root of population variance. See the STDEVPA tutorial. This is a powerful cross-check when converting a formula between equivalent measures.

Population versus sample: choose the denominator consciously

The exact numeric A data have total squared deviations 20. Population VARPA(A2:A5) is 20/4 = 5; sample VARA(A2:A5) is 20/3 ≈ 6.6666667. The VARPA value 5 was natively verified in this batch; the sample value is independently calculated, not a newly claimed sample-function native test. If you are estimating the variability of a larger population from observed cases, sample variance is typically more appropriate.

About error messages: the examples above are observations for 25.2.3.2. Even if an empty range returns an error, a single text entry returned zero in this test: avoid summarizing all text-only and empty ranges as equivalent. Missing arguments and cross-version behavior should be checked against your own installation. None of these tests establishes behavior in all locales or future releases.

ODF terminology note. LibreOffice’s VARPA entry does not attach the COM.MICROSOFT.VAR.P name used for the separate VAR.P entry. These names should not be interchanged without checking their input rules. See the LibreOffice official statistics page.

• VARP — whole-population variance that ignores text
• VAR.P — separate namespaced population-variance function
• VARA — sample variance that counts text as zero
• STDEVPA — population standard deviation that counts text as zero
• STDEVP — population standard deviation that ignores text

Native execution: 26/26 VARPA numerical assertions passed in LibreOffice Calc 25.2.3.2 Linux, using the saved .ods workbook and UNO-driven reproduction script. There are 12 separate function-specific diagnostic observations (not additional assertions). Independent calculation: standard population variance was computed with an independent algorithm on the given numeric/coerced arrays; comparison tolerance 2×10⁻⁷. Documentation checking: official vendor support for syntax, 255 positions, text handling, locale settings and cross-app notes as identified below. The results are labelled as observed in Calc 25.2.3.2, independently expected, or documentation only. The native test workbook does not execute Microsoft Excel or Google Sheets.

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

1. The Document Foundation, LibreOffice 26.8 Help, Statistical Functions Part Five, sections VARPA, VARP, VAR.P, VARA, STDEVPA — population versus sample, documented syntax, text/argument semantics, separate variant.

2. The Document Foundation, LibreOffice 26.8 Help, Formula Options, sections Use English function names and Separators — locale and formula-entry settings.

3. Google Docs Editors Help, VARPA, sections Syntax and Notes — cross-application documentation only, including Google single-value error behavior.

4. Google Docs Editors Help, STDEVPA, sections Notes and See Also — relationship between variance and standard deviation; documentation-only.

5. Microsoft Excel VARPA — function existence checked on 13 October 2026; no Excel execution.