DSTDEV in Excel: Sample Standard Deviation and Numeric Denominators

=DSTDEV(database, field, criteria)

The database range includes the header row. Field identifies the column to calculate: use a quoted heading such as “Units”, or its one-based position within the database. Here Units is field 3, even if the database is later moved to another part of the sheet. Criteria is a separate range containing matching field headers and at least one conditions row. All three arguments are required.

Enter this numeric database in A1:D7:

Put Team in F1 and Product in G1. In F2 enter ="=North"; in G2 enter ="=A". These formulas produce the exact-match criteria text =North and =A. The range F1:G2 selects the three North/A records, whose Units values are 2, 4 and 6 and Sales values are 100, 200 and 300.

Conditions in the same criteria row act together with AND. Alternative criteria rows act with OR. Keep the criteria and output cells outside the database, and leave room for the data to grow.

The selected values 2, 4 and 6 have mean 4. Their deviations are −2, 0 and 2, so squared deviations total 8. The numeric observation count is three. The sample denominator is 3−1=2.

The result is square root of 4, giving 2. Standard deviation is in the original measurement units. The formula =DSTDEV(A1:D7,3,F1:G2) uses the same Units field by index and has the same independent target.

=DVAR(A1:D7,"Units",F1:G2) has expected result 4. Squaring the standard deviation recovers the corresponding variance. Use full calculated values rather than squaring or taking roots of rounded display text.

The population counterpart DSTDEVP has expected result 1.632993162. It uses a different denominator. Filtering records does not decide whether the selected data should be treated as a sample or a complete population; that choice belongs to the analysis.

Build a separate two-column database in A12:B18 with headers Team and Units and six rows North/2, North/4, North/6, North/2, North/4, North/6. Put Team in F12 and ="=North" in F13. Use =DSTDEV(A12:B18,"Units",F12:F13).

The mean stays four, squared deviations total 16, and the count is six. The expected sample standard deviation is approximately 1.788854382. The sample denominator changes from two to five; duplicating the data does not preserve the sample statistic.

This mathematical comparison does not justify retaining accidental duplicate records. Counts, totals and the intended observation model can still be wrong.

For a separate copy of the selected readings, add 100 to obtain 102, 104 and 106. The mean shifts to 104, but all deviations from that new mean are unchanged, so sample standard deviation stays at its original value.

Multiply the original values by three to obtain 6, 12 and 18. The expected result becomes 6. Standard deviation scales by the absolute multiplier. This distinction is essential when changing measurement units.

With an unrestricted criterion consisting of a Team header and one physically blank condition cell, the Units dataset becomes 2, 9, 10, 4, 7 and 6. Its mean is 38/6, approximately 6.333333333, and its squared-deviation total is 136/3, approximately 45.333333333.

The expected sample standard deviation is approximately 3.011090611. The center has changed, so an average of subgroup dispersion values does not generally reconstruct the full-database spread. Keep the raw observations or sufficient group counts and means when combining summaries.

A sample variance requires at least two numeric observations for its n−1 denominator. A single observation does not supply an ordinary sample-spread estimate. The dedicated reference used here does not specify a complete native no-match and insufficient-data error table, so exact boundary error codes are not invented.

Check the numeric-field count alongside the result. The examples deliberately use ordinary numeric values and do not certify mixed errors, numeric text, formula-empty strings, or highly ill-conditioned native calculations.

Use the complete header-and-condition range, not just the condition cells. Check the field heading for spelling and spacing, and verify that a numeric field index still points to the intended column after a layout change. A worksheet’s visible rows are not a substitute for the explicit database range and criteria used in the formula.

Do not include an accidental empty conditions row in the criteria range: an unrestricted alternative can broaden selection. Microsoft’s DAVERAGE reference documents the shared AND/OR, exact-text and case-insensitive matching rules. These examples avoid wildcard, case-sensitive-formula and numeric-text complications.

The database functions do not validate whether the selected records are the right records for the question. Keep a matching-record or numeric-field count beside an important result. Native blank/formula-empty handling, coercion, extreme precision and error propagation not explicitly documented in this guide remain unverified.

• Current documentation: the dedicated Microsoft reference lists Excel for Microsoft 365, Excel for Microsoft 365 for Mac, Excel 2024, Excel 2024 for Mac, Excel 2021, Excel 2021 for Mac, Excel 2019, Excel 2016.
• Introduced: the first introduction version/build is not established by the dedicated reference used here.
• Last functioning version: unknown; a current help listing is not a last-working-build test.
• Evidence checked: October 15, 2026. Official documentation was freshly retrieved; examples were independently calculated. No native Excel, Mac or web-client execution was performed.

• Microsoft DSTDEV reference.
• Microsoft function catalog and introduction-marker explanation.
• Editorial testing standards.

Examples use English function names and comma separators. Expected numbers are rounded independent mathematical calculations; documented error expectations are labeled as such. They are not captured Excel output. All illustrative data and policies are fictional.