How to Use the DSTDEV and DSTDEVP Functions in Excel
The DSTDEV and DSTDEVP functions in Excel calculate a standard deviation. DSTDEV calculates the standard deviation for a sample. DSTDEVP calculates the standard deviation for a population. As with other database statistical functions, the unique and truly useful feature of DSTDEV and DSTDEVP is that you can specify that you want only list records that meet the specified criteria you include in your calculations.
If you want to calculate standard deviations without first applying selection criteria, use one of the Excel non-database statistical functions such as STDEV, STDEVA, STDEVP, or STDEVPA.
The DSTDEV and DSTDEVP functions use the same syntax:
where database is a range reference to the Excel table that holds the values for which you want to calculate a standard deviation, field tells Excel which column in the database to use in the calculations, and criteria is a range reference that identifies the fields and values used to define your selection criteria.
The field argument can be a cell reference holding the field name, the field name enclosed in quotation marks, or a number that identifies the column (1 for the first column, 2 for the second column, and so on).
As an example of how the DSTDEV function works, suppose you construct this worksheet.
The worksheet range holds a small list with row 1 storing field names (Name, State, and Donation) and rows 2 through 11 storing individual records.
Rows 14 and 15 store the criteria range. The criteria range typically duplicates the row of field names. The criteria range also includes at least one other row of labels or values or Boolean logic expressions that the DSTDEV and DSTDEVP functions use to select records from the list.
Note the Boolean expression in cell C15, <250, which tells the function to include only records where the Donation field shows a value less than 250.
The DSTDEV function, which appears in cell F3, is
and it returns the sample standard deviation of the donation amounts shown in the database list, excluding the donation from Jeannie in California because that amount is not less than 250. The actual function result is 33.33333.
The DSTDEVP function, which appears in cell F5, is
and returns the population standard deviation of the donation amounts shown in the database list excluding the donation from Jeannie in California because that amount isn’t less than 250. The actual function result is 31.42697.
You wouldn’t, by the way, simply pick one of the two database standard deviation functions willy-nilly. If you’re calculating a standard deviation using a sample, or subset of items, from the entire data set, or population, you use the DSTDEV function. If you’re calculating a standard deviation using all the items in the population, use the DSTDEVP function.