How to Use Excel's DSTDEV Function

By eHow Computers Editor

Rate: (0 Ratings)

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.

Flag Article

Instructions

Difficulty: Moderate

Step1

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.

Step2

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.)

Step3

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.

Step4

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.

Step5

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.

Step6

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.