How to Use Excel's DSTDEV Function

Excel's DSTDEV function is short for "database standard deviation." It calculates a population's standard deviation based on a sample for values in the column of a database that match the specified criteria. When the values have been selected, DSTDEV then calculates its return value in the same manner as STDEV. The following steps will show how to use Excel's DSTDEV function.

Instructions

1. Learn the syntax of DSTDEV (database, field, criteria). Database is an array that specifies the database. Field is a column in the database from which DSTDEV will calculate the standard deviation and criteria is a cell range specifying the criteria to be used in selecting the rows.

2. Know that, like all database functions, DSTDEV uses its arguments to construct a query that returns a result set. The values from the specified column are then used as the arguments for the non-database equivalent function (STDEV in the case of DSTDEV.)

3. Calculate the standard deviation using values taken from the specified column of the selected rows. Unlike STDEVP, STDEV assumes its input values are a sample and not the entire sample. First determine the arithmetic mean of the population by dividing the population sum by the population count.

4. Determine the standard deviation based on a sample by the equation ((sum of (x-m)^2)/(n-1))^1/2 where x is each value of the population, m is the mean of the population and n is the population count.

5. Enter the following in the first four columns of an Excel spreadsheet:

   Row 1: Tree, Height, Age and Yield;
   Row 2: ="=Apple" and =">10" (nothing in columns three and four);
   Row 3: ="=Pear" (nothing in columns two, three and four);
   Row 4: Tree, Height, Age and Yield;
   Row 5: Apple, 18, 20 and 14;
   Row 6: Pear, 12, 12 and 10;
   Row 7: Cherry, 13, 14 and 9;
   Row 8: Apple, 14, 15 and 10;
   Row 9: Pear, 9, 8 and 8; and
   Row 10: Apple, 8, 9 and 6.

6. Interpret the formula =DSTDEV(A4:D10,"Age",A1:A2) as follows: The database is defined as the array A4:D10. Note the column names in the first row of this array. "Yield" is the column name from which the values will be taken. The criteria are given by A1:A2 which indicates that the column named Tree must contain "Apple". DSTDEV(A4:D10,"Yield",A1:A2) will therefore be evaluated as STDEV(14,10,6)=4.