DSTDEV function
DSTDEV estimates the standard deviation of one column in a list, treating the rows that match your criteria as a sample. It needs at least two matching numbers.
Example
Read it as a sentence: in the table A1:C5, estimate the standard deviation of the Qty values for the rows that match the criteria in E1:E2.
A
B
C
D
1
0.707106781186548
2
3
Syntax
DSTDEV(database, field, criteria)
-
database(required) - Range (with header row) that holds the list.
-
field(required) - Column to use, given as its header text in quotes or as a column number.
-
criteria(required) - Range with matching headers on top and conditions underneath.
Returns: Sample standard deviation of matching values.
What Excel actually returns
Each formula below was run in desktop Excel. The result is exactly what the cell showed, errors included.
| Formula | Excel shows | Case |
|---|---|---|
=DSTDEV(Data!Q1:S6,"Qty",Data!U1:U2) | 14.14213562 | Criteria Item=Apple (2 rows) |
=DSTDEV(Data!Q1:S6,"Qty",Data!Z1:Z3) | 10 | OR criteria (two rows) |
=DSTDEV(Data!Q1:S6,"Qty",Data!AA1:AA2) | 10 | Numeric criteria >15 |
=DSTDEV(Data!Q1:S6,"Qty",Data!AB1:AB2) | 12.90994449 | Blank criterion matches all |
=DSTDEV(Data!Q1:S6,"Qty",Data!AD1:AD2) | 14.14213562 | Wildcard A* |
=DSTDEV(Data!Q1:S6,"Qty",Data!AN1:AN2) | 14.14213562 | Case-insensitive apple |
=DSTDEV(Data!Q1:S6,"Qty",Data!V1:V2) | #DIV/0! | Criteria Item=Pear (1 row) |
=DSTDEV(Data!Q1:S6,"Qty",Data!W1:W2) | #DIV/0! | No matching rows |
References with a sheet name, like Items!, point at sample tables in our test workbook.
Watch out for
- Criteria headers must match the database headers exactly.
- Needs at least two matching numbers.
- Excel refuses the formula outright if you type an array constant such as {1,2,3} where a range is expected; point it at cells instead (measured in Excel 16.0.20527).
Where it works
- Excel
- DSTDEV is in every version of Excel from 2007 on.
- Google Sheets
- Same name, same behaviour for everyday use. Sheets has it, but takes cell ranges; array-constant databases are Excel-only.
Part of Database functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .