DSTDEV function in Excel

What is DSTDEV Function in Excel?

The DSTDEV function is one of the (Database) functions of Excel.

It estimates the standard deviation based on a sample from selected database entries.

We can find this function in the Database of the insert function Tab.

How to use DSTDEV function in excel

1. Click on an empty cell (like F5 )

2. Click on the fx icon (or press shift+F3)

3. In the insert function tab you will see all functions

4. Select Database category

5. Select DSTDEV function

6. Then select ok

7. In the function arguments Tab you will see DSTDEV function

8. In the Database section you can enter the range of cells that makes up the list or database. A database is a list of related data (ex: Table1)

9. Field section is either the label of the column in double quotation marks or a number that represents the column’s position in the list

10. Criteria section is the range of cells that contains the conditions you specify. The range includes a column label and one cell below the label for a condition (ex: Table1)

11. You will see the result in formula result section

Examples DSTDEV function in excel

Example1:

calculate STDEV of student height with DSTDEV function

=DSTDEV(A5:D14,"Height",B1:D2)----->>>>answer is  3.6

Example 2:

calculate STDEV of student age with DSTDEV function

=DSTDEV(A5:D14,"Age",C1:D2)----->>>>answer is  3.26

Example 3:

Python code for DSTDEV function

import statistics

age=[25, 25, 27, 22, 29 ,29,21, 25 ,22]

stdev_age=statistics.stdev(age)

print(stdev_age)

What is the purpose of DSTDEV function?

It estimates the standard deviation based on a sample from selected database entries.

What is the Return value  of DSTDEV function?

It just returns the number.

DSTDEV (database, field, criteria)=number

How many arguments does DSTDEV function have?

DGET(database, field, criteria)

DSTDEV function has 3 arguments.

• In the Database section you can enter the range of cells that make up the list or database
• Field section is either the label of the column in double quotation marks
• Criteria section is the range of cells that contains the conditions you specify

Which version of excel supports DSTDEV function?

this function is available for all excel versions (2003-2019)

Errors in DSTDEV function

If no record matches the criteria, DSTDEV returns the #DIV/0! error value.

If just one record matches the criteria, DSTDEV returns the #DIV/0! error value.

=DSTDEV(Table1[All],"Age",C1:D2)----->>>>answer is  #DIV/0! 

Conditions and criteria range in DSTDEV function

Criteria argument in DSTDEV  function

we can define any range for the criteria argument: it has 2 condition

1. At leastone column label

2. At least one cell under the column label.

For example, if the range A1:A2 contains Name in A1 and Benjamin in A2,

you could define A1=”Name” & A2=”Benjamin”

Dget function find the word of “Benjamin” under “name” column

location of Criteria range in DSTDEV  function

Although the criteria range can be located anywhere on the worksheet, do not place the criteria range below the list. If you add more information to the list, the new information is added to the first row below the list. If the row below the list is not blank, Microsoft Excel cannot add the new information.

Make sure that the criteria range does not overlap the list.

To perform an operation on an entire column in a database, enter a blank line below the column labels in the criteria range.

DSTDEV related functions :

• DCOUNT Function
• DCOUNTA Function
• DMAX Function
• DVARP Function
• DVAR Function
• DSTDEVP Function