HomeGoogle DocsSpreadsheetStandard Deviation - DSTDEV Database Function in Google Sheets

Standard Deviation – DSTDEV Database Function in Google Sheets

Published on

The DSTDEV database function plays an important role in standard deviation (of a population sample) calculation in Google Sheets.

It’s more efficient compared to its ‘sibling’ STDEV as it works on structured data and also takes criteria as an argument.

Instead of a partial data (population sample), if you are working on a full data (population), then there is another function called DSTDEVP (that’s the topic of my another tutorial which is in the pipeline) or STDEVP.

In the normal STDEV function, to include criteria while calculating standard deviation, you must use the function Filter.

You can find an example of this combination at the last part under the subheading STDEV vs DSTDEV in Google Sheets.

To use your data range/array in database functions, include a header row contain field labels. Don’t worry! All these you can learn easily with the help of this tutorial.

How to Use DSTDEV Database Function in Google Sheets

Syntax:

DSTDEV(database, field, criteria)

database – The range of cells containing the data to consider. The columns in the range are known as fields. The first row of this range must contain the labels (field labels) for each column’s (field’s) values.

See the screenshot below in which the first row contains field labels which are “Dog Breed”, “Height (mm)” and “Weight (lbs)”.

field – Indicates which column in the range (database) contains the values to be used. You can specify the fields by entering the field labels within double quotes or use the column number. The first column in the database has the field number 1.

In my example below it’s “Height (mm)” or you can use the number 2 as the field.

criteria – Conditions to be included in the calculation.

DSTDEV Formula Examples in Docs Sheets

Here are few formulas in that I am going to use single, multiple and no criteria in standard deviation calculation.

My sample data contains the names of a few dog breeds and their height and weight. I am only using the height column in the calculation.

Example to DSTDEV Database Function in Google Sheets

Example 1: DSTDEV Without Criteria/Conditions

=DSTDEV(A1:C12,2,E1:G2)

Note: If you don’t have any criteria to specify, you can simply use the STDEV function as below.

=stdev(B2:B12)

Both the formulas will return the standard deviation output as below.

Result: 125.41

In this formula, I have used field # 2 that contains the heights of dog breeds. This helps us to find how the heights of dogs are spread out.

For your information, the average (mean) height of the dog breeds in this population sample is 294.

=average(B2:B12)
Standard deviation chart in Google Sheets

Want to know how to plot this chart? Follow this guide – Mean and Standard Deviation Straight Lines on a Column Chart in Google Sheets.

Example 2: DSTDEV With Criteria/Conditions

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”. If so, you can use the formula below.

Enter the string “Pug” in cell E2 without quotes. The formula will be the same which I have used in example # 1 above.

=DSTDEV(A1:C12,2,E1:G2)

It will return the result 13.61.

In the above formula, if you want, you can create a virtual array to replace the criteria range E1:G2 as below.

=DSTDEV(A1:C12,2,{"Dog Breed";"Pug"})

I have an advanced guide to use conditions in database functions in Google Docs Sheets – The Ultimate Guide to Using Criteria in Database Functions in Google Sheets. This can help you to become an expert in using any D’ functions in Sheets.

STDEV vs DSTDEV in Google Sheets

Actually I have already explained the differences above. Read that in one place below.

  1. The DSTDEV is a database function that requires structured data. On the other hand, STDEV is not a database function. So it works in normal data ranges.
  2. You can filter data using DSTDEV. Just provide the condition in the criteria argument to filter. But in STDEV there is no option, though there are workarounds.

The above are the two differences between these functions. In the above point # 2, I have mentioned a workaround, right?

See this example formula which will return the standard deviation 13.61. It’s equal to the formula output given under the title “Example 2: DSTDEV With Criteria/Conditions”.

=stdev(FILTER(B2:B12,A2:A12="pug"))

Any doubt regarding the use of DSTDEV database function in Google Sheets, please drop a line in the comments.

Related: How to Use the VAR Function in Google Sheets.

Prashanth KV
Prashanth KVhttps://infoinspired.com/about-me/
Introducing 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.

Creating a Running Balance with Dynamic Array Formulas in Excel

For calculating a running balance, we can utilize the SCAN function, which is a...

Comparing the FILTER Function in Excel and Google Sheets

I believe comparing the differences in how the FILTER functions work in Excel and...

Quartile IF in Google Sheets

To calculate quartiles based on conditions, referred to as "Quartile IF," we will use...

Drop Rows and Columns by Index Numbers in Google Sheets

With the help of two simple custom formulas, we can drop rows and columns...

More like this

Quartile IF in Google Sheets

To calculate quartiles based on conditions, referred to as "Quartile IF," we will use...

Drop Rows and Columns by Index Numbers in Google Sheets

With the help of two simple custom formulas, we can drop rows and columns...

Reference a Column by Field Label in Google Sheets QUERY

Here we'll explore the two best methods for referencing a column by its field...

LEAVE A REPLY

Please enter your comment!
Please enter your name here