DSTDEV in WPS Spreadsheet: Sample Spread for Matching Database Rows
Calculate sample standard deviation for matching database records with DSTDEV, include headers correctly and independently verify the n−1 calculation.
Verification status: See the evidence and test scope described in this guide. How formula examples are checked
DSTDEV selects records using a criteria range and calculates the sample standard deviation of a chosen numeric field. It answers a different question from the spread of all records: selection occurs before the mean and squared deviations are calculated.
For a selected numeric sample of size n, calculate its mean, sum the squared deviations from that mean, divide by n−1, then take the square root. The answer has the same units as the measured field. It is not the variance, and it is not the population-standard-deviation calculation that divides by n.
Syntax and table structure
=DSTDEV(database, field, criteria)
Database includes a header row and data records. Field identifies the measured column using its quoted header or its 1-based column position. Criteria includes a matching column header with a condition underneath. The WPS lesson also demonstrates passing a cell containing a field header. Official DSTDEV lesson
The examples here use one exact text criterion, clean numeric values and at least two selected records. Wildcards, multi-condition combinations, text-valued measures and no-match errors are not inferred from other spreadsheet products.
Build an original sample database
Enter this fictional table in A1:B5:
| Group | Score |
|---|---|
| A | 2 |
| A | 4 |
| A | 6 |
| B | 100 |
Put Group in D1 and A in D2. Leave the output outside both the database and criteria ranges, for example in F2:
=DSTDEV(A1:B5,"Score",D1:D2)
The independently calculated expectation is 2. The selected values are 2, 4, 6; their mean is 4; squared deviations are 4, 0, 4. Dividing their sum 8 by n−1=2 gives variance 4, and its square root is 2. The 100 belongs to group B and does not enter this calculation.
Using =DSTDEV(A1:B5,2,D1:D2) identifies the same Score field by position. Using B1 as the field argument refers to the cell containing the header, as in the vendor’s demonstrated pattern. Header labels must actually match the intended columns.
Independent selected-sample controls
Keep the same header/criteria layout and one excluded group B record. Replace the group A score values as follows:
| Selected scores | Sample mean | Sample standard deviation |
|---|---|---|
| 2, 4, 6 | 4 | 2 |
| 10, 14, 18 | 14 | 4 |
| 2, 2, 2 | 2 | 0 |
| 1, 4, 7 | 4 | 3 |
All four datasets contain three selected numbers. Zero spread with three equal values is a valid mathematical result; it is different from having too few selected records. A two-record sample 2, 6 would have squared deviations 8 and divisor 1, giving sqrt(8), approximately 2.82842712474619.
Practical case: separate selection from measurement
Suppose group A denotes one fictional production batch and group B another. Changing group B’s excluded score from 100 to 1000 should not change the reference expectation for group A: the selected values are still 2, 4, 6. By contrast, changing one selected value changes the mean and every deviation.
For example, selected values 2, 4, 10 have mean 16/3. Squared deviations total 104/3; dividing by 2 gives 52/3, so the sample standard deviation is sqrt(52/3), approximately 4.16333199893227. This illustrates why a filtered calculation must use the selected sample’s mean, rather than a mean calculated from the entire database.
Use the statistic as a descriptive quantity alongside sample count and context. It does not establish that observations are independent, that a process is stable, or that a difference between batches is statistically significant.
Source correction and troubleshooting
The lesson’s prose names F2 as an output cell, but its actual final screenshot uses G2 for the output and F1:F2 for criteria. Those ranges cannot safely serve both purposes. The original layout above deliberately uses D1:D2 for criteria and F2 for the result, keeping the roles separate.
The published screenshot selects ages 36, 40, 45 and displays approximately 4.509249753, consistent with the sample divisor n−1. That supports the sample interpretation; it is not a native run of the original Score table.
The reviewed page does not give a complete invalid-header, singleton or no-match error table. Check the included header row, exact criterion label and numeric selected count before interpreting a result. Do not invent a specific error token for these unverified boundaries.
DCOUNT can help inspect selected numeric records; DAVERAGE concerns their mean; STDEV concerns sample spread without this database-criteria interface.
Availability and evidence scope
The official WPS lesson is dated 2019-06-25 and explicitly applies to WPS Office for Windows Personal. Its numeric build is unspecified. This is a documented-by date, not a function introduction date; the first supported version and last functioning build remain unknown. The lesson does not establish Linux or other-edition parity, nor a retirement or deprecation history.
The complete text and vendor walkthrough were inspected on October 18, 2026. The original examples in this guide are documentation-derived and independently calculated, not executed in WPS or another spreadsheet engine. Vendor walkthrough observations are identified separately and are not native tests of these examples.