Google Sheets STDEVA: When Text Becomes Zero in Sample Spread
Use STDEVA in Google Sheets with a checked mixed-cell example. Compare text-as-zero with ignoring text, and separate a missing observation from a genuine zero.
Verification status: See the evidence and test scope described in this guide. How formula examples are checked
Quick answer: STDEVA estimates sample standard deviation while counting text as zero. For cells containing 2, 4, 6 and the text “not recorded”, =STDEVA(A2:A5) is expected to return about 2.581989, not the 2 obtained by ignoring the text cell.
Verification: Reviewed October 21, 2026 against Google’s documentation. The original examples use explicit numeric cells and a nonnumeric text label, with results independently checked outside Google Sheets. No native application run or screenshot is claimed. Booleans, empty cells, numeric-looking text and formula-generated empty strings are outside this guide’s verified scope.
Syntax and what the choice means
=STDEVA(value1, [value2, ...])
The first input can be a value or range; additional inputs are optional. Google documents sample standard deviation, conversion of encountered text to zero, a minimum of two values and equivalence to SQRT(VARA(...)) for the same data. For a whole-population calculation with text-as-zero treatment, its reference points to STDEVPA. See the official STDEVA reference.
The function name does not decide whether converting a label to zero makes sense. A measured delay of zero minutes means the event occurred immediately. “Not recorded” means the delay is unknown. Those are different observations even if a calculation can turn both into the same number.
Example: inspect the effect of a missing-data label
In a spare sheet, enter the following synthetic dispatch-delay sample. Put the heading Delay in minutes in A1 and exclude that heading from every calculation.
| Cell | Entry | Meaning |
|---|---|---|
| A2 | 2 | Recorded numeric delay |
| A3 | 4 | Recorded numeric delay |
| A4 | 6 | Recorded numeric delay |
| A5 | not recorded | Text label, not a measured zero |
Enter these formulas outside the input range:
| Cell | Formula | Expected result |
|---|---|---|
| C2 | =STDEVA(A2:A5) |
Approximately 2.581989 |
| C3 | =STDEV(A2:A5) |
2 |
| C4 | =SQRT(VARA(A2:A5)) |
Approximately 2.581989 |
Google’s STDEV documentation distinguishes text supplied directly from text in referenced cells: referenced text cells are ignored. C3 deliberately uses a range, not a text literal argument.
The independent STDEVA model is the four-value sample 2, 4, 6, 0. Its mean is 3. Squared distances from the mean are 1, 1, 9 and 9, totalling 20. Divide by the sample denominator, 4 − 1 = 3, to obtain variance 20/3. Its square root is approximately 2.581988897.
For C3, the three retained numbers are 2, 4, 6. Their mean is 4; squared distances total 8. Dividing by 3 − 1 gives variance 4 and standard deviation 2. Neither number is automatically the right summary of the original process: first decide how missing observations should be handled and disclose that choice.
Mutation: replace the label with a measured value
Change A5 from the text label to numeric 4. The sample is now 2, 4, 6, 4. Expected C2 and C3 both become approximately 1.632993; C4 should agree with C2.
Independently, the mean is 4, the squared distances total 8, and the sample variance is 8/3. The square root is approximately 1.632993162. This check exposes a formula accidentally ending at A4: that stale range would still return 2 instead of incorporating the new observation.
As a separate controlled check, replace A5 with numeric 0. C2 returns to approximately 2.581989. The equal result does not prove that the former text label was valid data. It only demonstrates why reviewing the original cells matters: the text-as-zero transformation hides that distinction in the final statistic.
Troubleshooting
- Unexpectedly large spread: Look for labels inside the range, including an accidentally included header. Review which cells genuinely represent observations before changing the formula.
- A one-value calculation returns
#DIV/0!: Google’s reference documents this for fewer than two supplied values. For a simple check,=STDEVA(A2)with just the numeric 2 is insufficient for sample standard deviation. - The variance seems too large or has different units: Standard deviation is the square root of variance. C4 checks the same dataset through VARA; variance is in squared minutes, while C2 is in minutes.
- Two formulas disagree: Compare their exact ranges and text policy. Do not remove all zeros merely to make them agree, since a genuine zero can be a valid observation.
- The sheet contains logical values, blank cells or imported numeric strings: These examples do not establish their treatment. Inspect and validate those input types separately rather than extrapolating from the explicit nonnumeric label used here.
- You want the result to ignore missing observations: Use an explicitly reviewed numeric dataset and document exclusions. Changing a label to zero just to avoid an error changes the data, not merely the formatting.
Related guides and availability
See STDEV.S for numeric sample spread, VARA for the corresponding sample variance and STDEVPA for population spread. The Google Sheets function reference lists the broader set.
Scope is the rolling Google Sheets service and ordinary worksheet formulas. No fixed desktop release, introduction date, export parity or connected BigQuery behavior was verified. This example explains data handling; it does not determine whether a sample is representative of the population you want to describe.