fx
FunctionPedia
Quick Answer

STDEVA Function

Sample standard deviation that also counts text and logicals as numbers.

✓ Excel✓ Google SheetsExcel All versions

Syntax

STDEVA
(value1, [value2], ...)

Parameters

ParameterDescriptionRequired
value1The first value, range, or reference in the sample.Required
value2Additional values, ranges, or references (optional).Optional

Basic Example

Sample standard deviation of numeric values only

=STDEVA(10,20,30,40,50)
Result15.8113883

With only numbers present, STDEVA returns the same result as STDEV.

Advanced Examples

Example 1: Text and TRUE are counted

A range that mixes numbers, text, and a logical

Text becomes 0 and TRUE becomes 1 before the calculation

=STDEVA(10,20,30,TRUE,"apple")
Result: 12.8156046
The values used are 10,20,30,1,0; the mean is 12.2, the sample variance is 164.2, and the square root is 12.8156046.

Example 2: Contrast with STDEV on a reference

Ignoring non-numeric cells

STDEV ignores text and logicals inside a range

=STDEV(A1:A5) where A1:A5 = 10,20,30,TRUE,"apple"
Result: 10
STDEV sees only 10,20,30 (n=3) and returns 10, whereas STDEVA counts all five values.

How STDEVA Works

STDEVA first coerces every argument to a number: numbers stay as-is, text (including empty text "") becomes 0, TRUE becomes 1 and FALSE becomes 0. It then computes the sample standard deviation with the n-1 denominator, so non-numeric entries increase both the sum and the count.

1
Identify values
Collect the values, including any text or logicals you want counted.
2
Enter formula
Type =STDEVA(range or values).
3
Press Enter
The sample standard deviation including non-numerics is shown.

Important Notes & Limitations

  • Counting text as 0 can distort the result if text cells are merely labels, not true zero measurements.

  • Still requires at least two effective values, otherwise returns #DIV/0!.

  • The n-1 denominator still assumes a sample, not a full population (use STDEVPA for population).

Common Errors & Fixes

#DIV/0!Fewer than two effective numeric values after coercion.

Fix: Provide at least two values that evaluate to numbers.

Larger spread than expectedText labels were coerced to 0, pulling the mean down.

Fix: Use STDEV when text cells should be ignored.

Download Practice File

Practice STDEVA with Real Data

Download a sample CSV file with pre-populated data and practice exercises for the STDEVA function. Works in both Excel and Google Sheets.

Works in Google SheetsCompatible with ExcelIncludes exercises

File format: CSV (comma-separated values) - opens in Excel, Google Sheets, and all spreadsheet apps

Frequently Asked Questions

Does STDEVA treat empty cells as 0?
No. Empty cells are ignored; only text and logicals are coerced (text to 0, TRUE to 1, FALSE to 0).
When should I use STDEVA instead of STDEV?
When your data includes logicals or text that genuinely represent 1/0 or zero, and you want them in the sample.
What is the population equivalent?
STDEVPA, which uses the n denominator instead of n-1.