STDEVA Function (LibreOffice Calc)

STDEVA estimates the sample standard deviation of a dataset and treats text values as zero. Standard deviation measures dispersion in the same units as the input measurements. STDEVA finds each effective observation’s distance from the mean, squares it, sums the squares, divides by n − 1, then takes the nonnegative square root:

sample standard deviation = SQRT(SUM((each observation − sample mean)^2) / (count − 1))

In Calc, STDEVA is the text-aware counterpart of STDEV. It is equivalent to the square root of VARA for a common set of observations; that relationship was checked on numeric input. Do not select STDEVA automatically for measurement data containing N/A placeholders—the text-as-zero behavior can introduce artificial variation. LibreOffice official STDEVA Help (26.8 Help, section STDEVA, checked again 2026-10-13).

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

You need at least two effective observations; otherwise sample standard deviation is undefined. The maximum 255 argument positions comes from the official signature, not from native testing in this batch. There is no optional switch to ignore text or change the sample denominator. LibreOffice’s displayed syntax uses ;, whereas some users configure ,; formula names may be translated in localized installations. Consult LibreOffice Formula Options, which also documents Calc A1 versus Excel A1 reference styles.

In a new Calc sheet, enter the following dataset:

Then enter:

=STDEVA(A2:A5)

Expected and natively observed: approximately 2.58198889747161. The four values have mean 5; sum of squared deviations is 20; sample variance is 20/3; and the square root is ~2.58199. These steps were independently checked with Python’s sample variance calculation and reproduced in native LibreOffice. For inline arguments, =STDEVA(2;4;6;8) returned the same numeric result in a separate native assertion.

Why it divides by n − 1

STDEVA describes a sample, not an entire population. It uses three degrees of freedom for four valid observations. In contrast, STDEVPA computes population standard deviation with denominator n and text-as-zero treatment. For the simple numeric example above, the calculated population standard deviation is √(20/4) ≈ 2.2360679775, and =STDEVPA(Examples.A2:A5) was also natively observed as approximately 2.2360679775. This distinction is fundamental; choosing sample/population cannot be settled merely by whether the dataset looks large.

Set up B2:B5 as 2, 4, n/a as text, 8.

=STDEVA(B2:B5)

Natively observed: 3.41565025531987 (√(35/3)). In this dataset the text entry contributes zero; the effective sample is [2, 4, 0, 8], its mean is 3.5, and the squared deviations sum to 35. If the text represents missing rather than a recorded zero, STDEVA is the wrong statistic. STDEV(B2:B5) would ignore the text and calculate √(28/3) ≈ 3.0550504633 from numeric values [2, 4, 8]. That numeric-only =STDEV(Examples.B2:B5) expression was also natively executed and returned about 3.05505046330389.

Set D2=2, D3=4, leave D4 genuinely empty, D5=8:

=STDEVA(D2:D5)

Natively observed: 3.05505046330389, from the three numbers alone. Entering literal missing in D4, rather than leaving it empty, changes the calculation to include zero, as in the previous example. Formatting, a blank-looking string, and a truly empty cell are not interchangeable. Empty-string formulas (such as ="") were not specifically examined in this batch, so do not generalize blank-cell findings to every kind of visually empty cell.

Set C2=2, C3=4, C4 to the formula =TRUE(), C5=8:

=STDEVA(C2:C5)

Natively observed: 3.09569593683445 (√(115/12)). Native Calc treated TRUE() as 1; the effective observations were [2, 4, 1, 8]. =STDEVA(TRUE();0;2) also returned 1 in native tests. These outputs are version-specific observations; do not infer that imported Boolean/text data has identical evaluation rules in Excel or Sheets without validation.

1. Derive standard deviation from VARA. On A2:A5 as above:

=SQRT(VARA(A2:A5))

Expected and independently calculated: 2.58198889747161; the native test of =SQRT(VARA(Examples.A2:A5)) matched this figure, as did =STDEVA(Examples.A2:A5). The underlying sample variance tutorial is VARA. This relation is useful for understanding units, but it is not a reason to write a more complicated formula when STDEVA alone communicates your intention.

2. Use two reference ranges as one sample. With A2:A5 populated:

=STDEVA(A2:A3;A4:A5)

The natively tested result is approximately 2.58198889747161. Each range contributes observations. Treating each group separately and averaging their two standard deviations is not equivalent; group means and sample sizes matter.

3. Quantify text-as-zero effects. With B2:B5 containing text n/a:

=STDEVA(B2:B5)-STDEV(B2:B5)

Independent arithmetic estimate: √(35/3) − √(28/3) ≈ 0.3605997920. This subtraction formula was also natively executed, returning approximately 0.360599792015973. The numerical difference does not mean either method is inherently more accurate; correctness depends on the meaning of the text entry.

4. Explore numeric-looking text. Put literal text '5 in E2, numeric 3 in E3, and numeric 7 in E4:

=STDEVA(E2:E4)

Natively observed: approximately 3.51188458428425, equal to √(37/3) for [0, 3, 7]. Although '5 looks like a number, it is stored as text and did not contribute five in native Calc. Convert imported numbers using an intentional data-cleaning step, then confirm cell types before running dispersion calculations. Don’t quietly reinterpret identification codes or labels as numeric data.

5. Detect uniform measurements. Put 6 in F2, F3 and F4:

=STDEVA(F2:F4)

Natively observed: exactly zero. All three readings are identical, so no dispersion is present in this simple dataset. Note that a zero result can also arise in all-text samples after they are mapped to zero; always verify why a standard deviation is zero.

6. Negative and decimal inputs. The sample [-2, 0, 2] has STDEVA = 2 and the sample [1.5, 2.5, 3.5] has STDEVA = 1. Both cases were natively validated. Negative observations are allowed as numeric inputs; unlike VARA’s squared-unit result, the standard deviation returns to the original units after taking the square root.

A set of 255 arguments is distinct from 255 rows inside one range; the Help does not claim a 255-observation dataset limit. Large numeric ranges, highly unequal magnitudes, international formats and complex array behavior were not exhaustively tested here. Use fixed sample inputs to verify portability when importing/exporting files.

These are distinct functions. For example, STDEV.S is also a documented Calc spelling, but do not assume it has every text-coercion rule of STDEVA; choose by the measurement policy, not by popularity.

LibreOffice’s own Help is the authority for the syntax and observed behavior here. Microsoft Excel and Google Sheets both document the function STDEVA; this confirms the name is available, not that every possible argument type or local separator behaves identically. In particular, workbook import, TRUE() and text-encoded numbers deserve comparison tests. Only LibreOffice Calc was executed for this guide. Cross-application support is documentation-checked via Microsoft STDEVA and Google Sheets STDEVA (checked again 2026-10-13). Use the destination application’s official instructions before converting formulas to comma-separated syntax.

Native testing: 22 predeclared numeric assertions for STDEVA matched independent calculations, with 18 separately labelled STDEVA edge-case diagnostics and eight additional cross-function diagnostics in the combined workbook (text, booleans, blanks and errors). Calc version 25.2.3.2, Linux, LibreOffice UNO. Full formula-by-formula results, initial expectations, actual native .Value/display/error code, a runnable local test script and ODS workbook are preserved in the saved validation package. Expected results were computed independently using Python statistics.variance with a square root; exploratory diagnostics are not counted as passed assertions. LibreOffice 26.8 Help is newer than this installed runtime; version scope is explicit.

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

• LibreOffice 26.8 Help, Statistical Functions Part Five, STDEVA, STDEV, STDEVPA, VARA headings — primary description, exact syntax, sample/population distinctions and treatment of text.
• LibreOffice 26.8 Help, Formula Options — configurable separators, formula reference style and localized names.
• Microsoft Support, STDEVA in Excel — existence and comparison, documentation only.
• Google Docs Editors Help, STDEVA — existence and comparison, documentation only.