DSTDEVP Function (LibreOffice Calc)
The DSTDEVP function calculates the population standard deviation of numeric values in a database column that match a set of criteria. It is part of the database function family and supports structured, criteria-based filtering.
Compatibility
▾| Excel | ✔ |
| Gnumeric | ✔ |
| Google_sheets | ✔ |
| Libreoffice | ✔ |
| Numbers | ✖ |
| Onlyoffice | ✔ |
| Openoffice | ✔ |
| Wps | ✔ |
| Zoho | ✔ |
What the DSTDEVP Function Does ▾
- Calculates population standard deviation of numeric values
- Applies criteria-based filtering using a criteria range
- Supports multiple criteria columns
- Supports AND/OR logic via criteria layout
- Works with structured database ranges
It is designed to be precise, statistical, and ideal for population-level standard deviation analysis.
Syntax ▾
DSTDEVP(database; field; criteria)
Arguments
-
database:
A range where the first row contains column labels. -
field:
The column to evaluate.- Use column label in quotes (recommended)
- Or use column index (1 = first column)
-
criteria:
A range containing column labels and one or more criteria rows.
Basic Examples ▾
Assume a table in A1:C6:
| A (Name) | B (Age) | C (Score) |
|---|---|---|
| John | 25 | 80 |
| Mary | 30 | 90 |
| Alex | 22 | 70 |
| John | 28 | 85 |
| Mary | 35 | 88 |
Criteria in E1:F2:
| Age | Score |
|---|---|
| >25 | >80 |
Population standard deviation of Score matching criteria
=DSTDEVP(A1:C6; "Score"; E1:F2)
Values: 90, 85, 88
Population variance = 4.222…
Population standard deviation = 2.0548…
Population standard deviation of Age matching criteria
=DSTDEVP(A1:C6; "Age"; E1:F2)
Values: 30, 28, 35
Population variance = 8.666…
Population standard deviation = 2.944…
Using field index
=DSTDEVP(A1:C6; 3; E1:F2)
Evaluates Score.
Advanced Examples ▾
STDEV.P of Score where Name = “John”
Criteria:
| Name |
|---|
| John |
Formula:
=DSTDEVP(A1:C6; "Score"; E1:E2)
STDEV.P of Age between 25 and 30
Criteria:
| Age |
|---|
| >=25 |
| <=30 |
Formula:
=DSTDEVP(A1:C6; "Age"; E1:E3)
OR logic (multiple rows)
Criteria:
| Age | Score |
|---|---|
| >30 | |
| >85 |
Formula:
=DSTDEVP(A1:C6; "Score"; E1:F3)
STDEV.P where Name begins with “M”
Criteria:
| Name |
|---|
| M* |
Formula:
=DSTDEVP(A1:C6; "Score"; E1:E2)
STDEV.P with dynamic criteria
=DSTDEVP(A1:C6; "Score"; H1:I2)
STDEV.P of positive Scores
Criteria:
| Score |
|---|
| >0 |
Formula:
=DSTDEVP(A1:C6; "Score"; E1:E2)
STDEV.P of non-blank Scores
Criteria:
| Score |
|---|
| <>"" |
Formula:
=DSTDEVP(A1:C6; "Score"; E1:E2)
Edge Cases and Behavior Details ▾
DSTDEVP evaluates only numeric values
Text, blanks, and errors are ignored.
Population standard deviation requires at least 1 numeric value
- 0 values →
#DIV/0! - 1 value → standard deviation = 0
field can be:
- Column label
- Column index
- Cell containing label
criteria must include column labels
Exact match required.
criteria supports:
- Comparison operators
- Wildcards
- Multiple rows (OR)
- Multiple columns (AND)
Empty criteria → STDEV.P of all numeric values
DSTDEVP of an error in database → error ignored
DSTDEVP of an error in criteria → error returned
Criteria rows:
- Each row = OR
- Each column = AND
Common Errors and Fixes ▾
#DIV/0! — No numeric values match
Fix:
- Broaden criteria
- Ensure numeric values exist
DSTDEVP returns 0 unexpectedly
Cause:
- Only one matching value
- Criteria labels don’t match database labels
Err:502 — Invalid argument
Occurs when:
- field is invalid
- database range malformed
Criteria not applied
Cause:
- Criteria labels not identical to database labels
Best Practices ▾
- Use DSTDEVP for population standard deviation
- Use DSTDEV for sample standard deviation
- Use column labels instead of index numbers
- Keep criteria ranges small and clearly labeled
- Use multiple criteria rows for OR logic
- Use multiple criteria columns for AND logic
- Use DVARP for population variance
- Use FILTER (modern Calc) for dynamic extraction
Related Patterns and Alternatives ▾
- Use DSTDEV for sample standard deviation
- Use DVAR and DVARP for variance
- Use DAVERAGE for conditional averages
- Use DSUM for summation
- Use DCOUNT or DCOUNTA for counting
- Use STDEV.P, STDEV.S for non-database STDEV
- Use FILTER for dynamic row extraction
By mastering DSTDEVP and its companion database functions, you can build powerful, structured, and criteria-driven statistical workflows in LibreOffice Calc.