DSTDEVP Function in Microsoft Excel
Introduction
The DSTDEVP function in Microsoft Excel is a database function that calculates the standard deviation of a population based on the entire population. It uses the numbers in a field (column) of records in a list or database that match the conditions that you specify.
Syntax & Arguments
The syntax for the DSTDEVP function is as follows:
DSTDEVP(database, field, criteria)
Arguments:
- Database: This is the range of cells that comprise the list or database. A database is a list of related data in which rows of related information are records and columns of data are fields. The first row of the list contains labels for each column.
- Field: This indicates which column is used in the function. Enter the column label enclosed between double quotation marks, such as “Age” or “Yield,” or a number (without quotation marks) that represents the position of the column within the list: 1 for the first column, 2 for the second column, and so on.
- Criteria: This is the range of cells containing the conditions you specify. You can use any content for the criteria argument as long as it includes at least one column label and at least one cell below the column label in which you specify a condition for the column.
The function returns the standard deviation of a population based on the entire population by using the numbers in a field (column) of records in a list or database that match the conditions you specify.
Examples
Example 1
Purpose: To calculate the standard deviation of sales volumes for a specific product category.
Data tables and formulas:
A | B | C | D | |
---|---|---|---|---|
1 | Product | Category | Sales | |
2 | Product A | Category 1 | 1200 | |
3 | Product B | Category 2 | 1500 | |
4 | Product C | Category 1 | 1300 | |
5 | Product D | Category 3 | 1100 | |
6 | Product E | Category 1 | 1400 | |
7 | Criteria | |||
8 | Category | |||
9 | Category 1 | |||
10 | Formula | |||
11 | =DSTDEVP(A2:C6, "Sales", A8:A9) |
Explanation: This formula calculates the standard deviation of product sales volumes in “Category 1”. The database range is A2:C6; the field is “Sales”; the criteria are specified in A8:A9. The result is the standard deviation of the sales volumes of the products that meet the requirements.
Example 2
Purpose: To calculate the standard deviation of the ages of employees in a specific department.
Data tables and formulas:
A | B | C | D | |
---|---|---|---|---|
1 | Employee | Department | Age | |
2 | Employee A | HR | 35 | |
3 | Employee B | Sales | 42 | |
4 | Employee C | HR | 39 | |
5 | Employee D | IT | 33 | |
6 | Employee E | HR | 37 | |
7 | Criteria | |||
8 | Department | |||
9 | HR | |||
10 | Formula | |||
11 | =DSTDEVP(A2:C6, "Age", A8:A9) |
Explanation: This formula calculates the standard deviation of the ages of employees in the HR department. The database range is A2:C6, the field is “Age”, and the criteria are specified in A8:A9. The result is the standard deviation of the ages of the employees that meet the requirements.
Example 3
Purpose: To calculate the standard deviation of the scores of students in a specific class.
Data tables and formulas:
A | B | C | D | |
---|---|---|---|---|
1 | Student | Class | Score | |
2 | Student A | Class 1 | 85 | |
3 | Student B | Class 2 | 90 | |
4 | Student C | Class 1 | 88 | |
5 | Student D | Class 3 | 92 | |
6 | Student E | Class 1 | 87 | |
7 | Criteria | |||
8 | Class | |||
9 | Class 1 | |||
10 | Formula | |||
11 | =DSTDEVP(A2:C6, "Score", A8:A9) |
Explanation: This formula calculates the standard deviation of the scores of students in “Class 1”. The database range is A2:C6, the field is “Score”, and the criteria are specified in A8:A9. The result is the standard deviation of the scores of the students that meet the requirements.
Tips and Tricks
- Make sure that the criteria range does not overlap the list.
- 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 count the further information.
- To operate on an entire column in a database, enter a blank line below the column labels in the criteria range.