Standard Deviation with the DSTDEV Function in Google Sheets

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 of the DSTDEV Database Function in Google Sheets

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.41

Where:

  • 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.41

In 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
Standard Deviation Chart in Google Sheets

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"))

Resources

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Hierarchical Numbering Sequences in Excel

Creating hierarchical numbering sequences in an Excel spreadsheet can significantly improve the way you...

How to Easily Repeat a Sequence of Numbers in Excel

Excel offers multiple ways to accomplish tasks, and the simplicity of each approach depends...

Create a Sequence of Dates at Every Nth Row in Excel (Dynamic Array)

Would you like to create a sequence of dates in every nth row in...

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.