The DSTDEV database function is essential for calculating the standard deviation of a population sample in Google Sheets. It is more efficient than its sibling function, STDEV, as it works with structured data and allows for criteria to be included in the calculation.
If you are working with the entire population rather than just a sample, you can use the DSTDEVP function or STDEVP.
When using the STDEV function, if you want to include criteria in your standard deviation calculation, you need to use the FILTER function. You can find an example of this combination at the end of this post under the subheading “STDEV vs. DSTDEV.”
To use your data range or array in database functions, make sure to include a header row with field labels. Don’t worry! You can easily learn all of this with the help of this tutorial.
Introduction to Standard Deviation
Standard deviation is a statistical measure that quantifies the variability within a dataset. It reveals how much individual data points deviate from the average value.
A low standard deviation indicates that the data points are clustered closely around the mean (average), while a high standard deviation signifies a wider spread of values.
As a side note, you can find a chart within this guide to help you understand the concept better, along with instructions on how to create that chart in the resources section.
How to Use the DSTDEV Database Function in Google Sheets
Syntax of the DSTDEV Function:
DSTDEV(database, field, criteria)- database: The range of cells containing the data to consider. The range contains columns known as fields, and the first row must include the field labels for each column.
- field: Indicates which column in the range (database) contains the values to be used. You can specify the fields by entering the field labels in double quotes or using the column number. The first column in the database is field number 1.
- criteria: Conditions that you include in the calculation.
DSTDEV Formula Examples in Google Sheets
Here are a few examples demonstrating how to use DSTDEV with single, multiple, and no criteria in standard deviation calculations.
My sample data includes several dog breeds and their corresponding heights and weights. I will focus on the height column for these calculations.
Example 1: DSTDEV Function with Empty Criteria
All arguments in database functions are mandatory. Even if you don’t want to specify criteria, you must provide an empty criteria range.
=DSTDEV(A1:C12, 2, E1:G2) // returns 125.41Where:
- database:
A1:C12 - field:
2 - criteria:
E1:G2
If you want to hard-code an empty criteria in the DSTDEV function, you can use two empty cells as an array constant as follows:
=DSTDEV(A1:C12, 2, {IF(,,); IF(,,)}) // returns 125.41In this formula, I have used field #2, which contains the heights of the dog breeds. This helps us understand how the heights of the dogs are distributed.
For your information, the average (mean) height of the dog breeds in this population sample is 294:
=AVERAGE(B2:B12) // returns 294.00
Example 2: DSTDEV Function With Criteria
One of the advantages of using the DSTDEV database function in Google Sheets over STDEV is its ability to include criteria.
Assume you want to find the standard deviation of the dog breed “Pug.” You can do this by entering “Pug” in cell E2 without quotes. The formula will be the same as in Example 1:
=DSTDEV(A1:C12, 2, E1:G2)This will return a result of 13.61.
In the formula above, you can also create a virtual array (array constant) to replace the criteria range E1:G2 as follows:
=DSTDEV(A1:C12, 2, {"Dog Breed"; "Pug"})STDEV vs. DSTDEV
I have already explained the differences above, but here’s a concise summary:
- DSTDEV is a database function that requires structured data, while STDEV is not a database function and works with regular data ranges.
- You can filter data using DSTDEV by providing conditions in the criteria argument, but STDEV does not have this option, although there are workarounds.
Here’s a formula that demonstrates one such workaround, returning a standard deviation of 13.61, which matches the output from “Example 2: DSTDEV With Criteria”:
=STDEV(FILTER(B2:B12, A2:A12 = "Pug"))
