The DSTDEV database function is a powerful tool in Google Sheets for calculating standard deviation. It offers enhanced efficiency compared to its counterpart, STDEV, as it works with structured data and allows for the inclusion of criteria. If you are working with a complete dataset (population) rather than a sample, you can use the DSTDEVP function or STDEVP, which will be covered in another tutorial.
In standard deviation calculations with the STDEV function, including criteria requires the use of the Filter function. However, with the DSTDEV database function, you can easily incorporate criteria directly into the calculation without any extra steps.
To use the DSTDEV database function, your data range or array must include a header row with field labels. Don’t worry, though, as this tutorial will guide you through the process.
How to Use DSTDEV Database Function in Google Sheets
The syntax for the DSTDEV function is as follows: DSTDEV(database, field, criteria).
- database – This refers to the range of cells that contain the data you want to consider. Each column in the range represents a field, and the first row of this range should contain the labels for each field.
- field – This indicates which column in the database contains the values you want to use for the calculation. You can specify the field by either entering the field label in double quotes or using the column number. The first column in the database is field number 1.
For example, if you want to use the “Height (mm)” column, you can write “Height (mm)” or use the number 2 as the field.
- criteria – This represents the conditions you want to include in the calculation.
DSTDEV Formula Examples in Docs Sheets
Let’s explore a few examples of DSTDEV formulas with varying criteria:
-
DSTDEV Without Criteria/Conditions
=DSTDEV(A1:C12,2,E1:G2)Note: If you don’t have any specific criteria to specify, you can simply use the STDEV function, like this:
=STDEV(B2:B12)Both formulas will return the standard deviation output, which in this case is 125.41. This formula uses field #2, which corresponds to the heights of the dog breeds. This allows us to determine how the heights of the dogs are spread out. As a side note, the average height of the dog breeds in this population sample is 294, which can be calculated using the AVERAGE function.
-
DSTDEV With Criteria/Conditions
Suppose you want to find the standard deviation of the dog breed “Pug.” In this case, you can use the following formula:=DSTDEV(A1:C12,2,E1:G2)By entering the string “Pug” in cell E2 (without quotes), the formula will return the result 13.61. Alternatively, you can create a virtual array to replace the criteria range E1:G2, as shown below:
=DSTDEV(A1:C12,2,{"Dog Breed";"Pug"})If you want to become an expert in using any D’ functions in Google Sheets, I have an advanced guide on using conditions in database functions. Feel free to check out “The Ultimate Guide to Using Criteria in Database Functions in Google Sheets” on Crawlan.com.
STDEV vs DSTDEV in Google Sheets
Here’s a quick summary of the differences between the STDEV and DSTDEV functions in Google Sheets:
- The DSTDEV function is a database function that requires structured data. On the other hand, STDEV is not a database function and works with normal data ranges.
- DSTDEV allows for data filtering by providing conditions in the criteria argument. In contrast, STDEV does not have this option, although there are workarounds available.
To illustrate the second point, consider the following formula, which gives the same standard deviation as the formula mentioned earlier in the “DSTDEV With Criteria/Conditions” example:
=STDEV(FILTER(B2:B12,A2:A12="pug"))If you have any questions about using the DSTDEV database function in Google Sheets, feel free to drop a line in the comments. Also, check out my related article on how to use the VAR function in Google Sheets on Crawlan.com.
