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.
Verification status: Selected examples were executed in LibreOffice Calc 25.2.3.2. This does not establish that every formula and edge case was tested. How formula examples are checked
Native-tested visual example: Standard deviation using database criteria
A database table contains scores for Alpha and Beta. The criterion selects only Alpha scores 10, 20, 30.
Enter this formula in cell E5:
=DSTDEVP(A4:C10;"Score";E8:E9)
Expected result: approximately 8.165 (population standard deviation). For Alpha, the population mean is 20. The variance is (100 + 0 + 100) / 3, so DSTDEVP returns approximately 8.165.
Real screenshot captured from LibreOffice Calc 25.2.3.2. The selected E5 cell shows the calculated result and the formula bar displays the entered formula.
Download the editable DSTDEVP example (.ods) — change the sample input cells and watch the calculation update.
Why it works: For Alpha, the population mean is 20. The variance is
(100 + 0 + 100) / 3, so DSTDEVP returns approximately 8.165. Watch for: The criterion heading must match a database heading exactly. DSTDEVP uses the population denominator, not the sample denominator.
Verification: Example calculated in LibreOffice Calc 25.2.3.2. Screenshot is a genuine application capture, not a mockup.
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.