Calculates the standard deviation of a population based on the entire population, using the numbers in a column of a list or database that match conditions you specify.
Syntax
DSTDEVP(database,field,criteria)
Database is the range of cells that makes up 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 indicates which column is used in the function. Field can be given as text with the column label enclosed between double quotation marks, such as "Age" or "Yield," or as a number that represents the position of the column within the list: 1 for the first column, 2 for the second column, and so on.
Criteria is the range of cells that contains the conditions you specify. You can use any range for the criteria argument, as long as it includes at least one column label and at least one cell below the column label for specifying a condition for the column.
The example may be easier to understand if you copy it to a blank spreadsheet.
- Create a blank spreadsheet.
- Select the example in the Help topic.
Selecting an example from Help
- Press CTRL+C.
- In the spreadsheet, select cell A1, and press CTRL+V.
- To switch between viewing the formula that returns the result and the result in the cell, select the cell and press F2 and then ENTER, or click Commands and Options on the spreadsheet toolbar, click the Formula tab, and look in the Formula in active cell (active cell) box.
| Tree | Height | Age | Yield | Profit | Height |
|---|---|---|---|---|---|
| Apple | >10 | <16 | |||
| Pear | |||||
| Tree | Height | Age | Yield | Profit | |
| Apple | 18 | 20 | 14 | 105.00 | |
| Pear | 12 | 12 | 10 | 96.00 | |
| Cherry | 13 | 14 | 9 | 105.00 | |
| Apple | 14 | 15 | 10 | 75.00 | |
| Pear | 9 | 8 | 8 | 76.80 | |
| Apple | 8 | 9 | 6 | 45.00 | |
| Formula | Description (Result) | ||||
| =DCOUNT(A4:E10,"Age",A1:F2) | This function looks at the records of apple trees between a height of 10 and 16 and counts how many of the Age fields in those records contain numbers. (1) | ||||
| =DCOUNTA(A4:E10,"Profit",A1:F2) | This function looks at the records of apple trees between a height of 10 and 16 and counts how many of the Profit fields in those records are not blank. (1) | ||||
| =DMAX(A4:E10,"Profit",A1:A3) | The maximum profit of apple and pear trees. (105) | ||||
| =DMIN(A4:E10,"Profit",A1:B2) | The minimum profit of apple trees over 10 in height. (75) | ||||
| =DSUM(A4:E10,"Profit",A1:A2) | The total profit from apple trees. (225) | ||||
| =DSUM(A4:E10,"Profit",A1:F2) | The total profit from apple trees with a height between 10 and 16. (75) | ||||
| =DPRODUCT(A4:E10,"Yield",A1:B2) | The product of the yields from apple trees with a height greater than 10. (140) | ||||
| =DAVERAGE(A4:E10,"Yield",A1:B2) | The average yield of apple trees over 10 feet in height. (12) | ||||
| =DAVERAGE(A4:E10,3,A4:E10) | The average age of all trees in the database. (13) | ||||
| =DSTDEV(A4:E10,"Yield",A1:A3) | The estimated standard deviation in the yield of apple and pear trees if the data in the database is only a sample of the total orchard population. (2.97) | ||||
| =DSTDEVP(A4:E10,"Yield",A1:A3) | The true standard deviation in the yield of apple and pear trees if the data in the database is the entire population. (2.65) | ||||
| =DVAR(A4:E10,"Yield",A1:A3) | The estimated variance in the yield of apple and pear trees if the data in the database is only a sample of the total orchard population. (8.8) | ||||
| =DVARP(A4:E10,"Yield",A1:A3) | The true variance in the yield of apple and pear trees if the data in the database is the entire orchard population. (7.04) | ||||
| =DGET(A4:E10,"Yield",A1:A3) | Returns the #NUM! error value because more than one record meets the criteria. |
- You can use any range for the criteria argument, as long as it includes at least one column label and at least one cell below the column label for specifying the condition. For example, if the range G1:G2 contains the column label Income in G1 and the amount 10,000 in G2, you could define the range as MatchIncome and use that name as the criteria argument in the database functions.
- In most cases, your criteria range should 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.
Multiple conditions in a single column
If you have two or more conditions for a single column, type the criteria directly below each other in separate rows. For example, the following criteria range displays the rows that contain either "Davolio," "Buchanan," or "Suyama" in the Salesperson column.
| Salesperson |
|---|
| Davolio |
| Buchanan |
| Suyama |
One condition in two or more columns
To find data that meets one condition in two or more columns, enter all the criteria in the same row of the criteria range. For example, the following criteria range displays all rows that contain "Produce" in the Type column, "Davolio" in the Salesperson column, and sales values greater than $1,000.
| Type | Salesperson | Sales |
|---|---|---|
| Produce | Davolio | >1000 |
One condition in one column or another
To find data that meets either a condition in one column or a condition in another column, enter the criteria in different rows of the criteria range. For example, the following criteria range displays all rows that contain either "Produce" in the Type column, "Davolio" in the Salesperson column, or sales values greater than $1,000.
| Type | Salesperson | Sales |
|---|---|---|
| Produce | ||
| Davolio | ||
| >1000 |
One of two sets of conditions for two columns
To find rows that meet one of two sets of conditions, where each set includes conditions for more than one column, type the criteria in separate rows. For example, the following criteria range displays the rows that contain both "Davolio" in the Salesperson column and sales values greater than $3,000, and also displays the rows for salesperson Buchanan with sales values greater than $1,500.
| Salesperson | Sales |
|---|---|
| Davolio | >3000 |
| Buchanan | >1500 |
More than two sets of conditions for one column
To find rows that meet greater than two sets of conditions, include multiple columns with the same column heading. For example, the following criteria range displays sales that are between 5,000 and 8,000 in addition to sales that are less than 500.
| Sales | Sales |
|---|---|
| >5000 | <8000 |
| <500 |
Conditions created as the result of a formula
You can use a calculated value that is the result of a formula as your criterion. When you use a formula to create a criterion, do not use a column label for a criteria label; either keep the criteria label blank or use a label that is not a column label in the list. For example, the following criteria range displays rows that have a value in column C greater than the average of cells C7:C10.
| =C7>AVERAGE($C$7:$C$10) |
Notes
-
The formula you use for a condition must use a relative reference to refer to the column label (for example, Sales) or the corresponding field in the first record. All other references in the formula must be absolute references, and the formula must evaluate to TRUE or FALSE. In the formula example, "C7" refers to the field (column C) for the first record (row 7) of the list.
-
You can use a column label in the formula instead of a relative cell reference or a range name. When an error value is displayed such as #NAME? or #VALUE! in the cell that contains the criterion, you can ignore this error because it does not affect how the list is filtered.
-
When evaluating data, there is no distinction between uppercase and lowercase characters.