fx
FunctionPedia
Quick Answer

STDEVPA Function

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

✓ Excel✓ Google SheetsExcel All versions

Syntax

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

Parameters

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

Basic Example

Population standard deviation of numeric values only

=STDEVPA(10,20,30,40,50)
Result14.14213562

With only numbers present, STDEVPA returns the same result as STDEVP.

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

=STDEVPA(10,20,30,TRUE,"apple")
Result: 11.4612426
The values used are 10,20,30,1,0; the mean is 12.2, the population variance is 131.36, and the square root is 11.4612426.

Example 2: Contrast with STDEVP on a reference

Ignoring non-numeric cells

STDEVP ignores text and logicals inside a range

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

How STDEVPA Works

STDEVPA coerces every argument to a number (text to 0, TRUE to 1, FALSE to 0), then computes the population standard deviation using the n denominator. Because it divides by n rather than n-1, it assumes the supplied values constitute the whole population.

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

Important Notes & Limitations

  • Counting text as 0 can distort the result if text cells are labels rather than true zeros.

  • Dividing by n assumes a full population; for a sample use STDEVA.

  • Requires at least one effective value, otherwise returns #DIV/0!.

Common Errors & Fixes

#DIV/0!No effective numeric values after coercion.

Fix: Provide at least one value that evaluates to a number.

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

Fix: Use STDEVP when text cells should be ignored.

Download Practice File

Practice STDEVPA with Real Data

Download a sample CSV file with pre-populated data and practice exercises for the STDEVPA 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 STDEVPA 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 STDEVPA instead of STDEVP?
When your data includes logicals or text that genuinely represent 1/0 or zero, and you want them in the population.
What is the sample equivalent?
STDEVA, which uses the n-1 denominator instead of n.