DSTDEV Function in Microsoft Excel
Part 1: Introduction
Definition
The DSTDEV function in Microsoft Excel is a database function that estimates the standard deviation of a population based on a sample. It uses the numbers in a field (column) of records in a list or database that match the conditions that you specify.
Purpose
The DSTDEV function is used when you want to estimate the standard deviation of a population using a sample. This is particularly useful in scenarios where you have a large dataset and want to calculate the standard deviation based on a subset of that data.
Syntax & Arguments
The syntax for the DSTDEV function is as follows:
DSTDEV(database, field, criteria) The DSTDEV function has the following 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. You can 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.
Return Value
The DSTDEV function returns the estimated standard deviation of a population based on a sample.
Remarks
- 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 in which you specify a condition for the column.
- Although the criteria range can be located anywhere on the worksheet, do not place the criteria field 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.
- Make sure that the criteria range does not overlap the list.
- To operate on an entire column in a database, enter a blank line below the column labels in the criteria range.
Part 2: Examples
Example 1
Purpose of Example
This example demonstrates how to use the DSTDEV function to estimate the standard deviation of the profit from selling different types of fruits.
Data Table and Formulas
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Fruit | Weight | Age | Yield | Profit |
| 2 | Apple | >10 | >200 | ||
| 3 | <16 | ||||
| 4 | |||||
| 5 | Fruit | Weight | Age | Yield | Profit |
| 6 | Apple | 18 | 20 | 14 | $105 |
| 7 | Pear | 12 | 12 | 10 | $96 |
| 8 | Cherry | 13 | 14 | 9 | $105 |
| 9 | Apple | 14 | 15 | 10 | $75 |
| 10 | Pear | 9 | 8 | 8 | $77 |
| 11 | Apple | 8 | 9 | 6 | $45 |
| 12 | |||||
| 13 | Formula | Description | Result | ||
| 14 | =DSTDEV(A5:E11, “Profit”, A1:B3) | The estimated standard deviation of the profit from selling apples. | 30 |
Result Table
| Formula | Description | Result |
|---|---|---|
| =DSTDEV(A5:E11, “Profit”, A1:B3) | The estimated standard deviation of the profit from selling apples. | 30 |
Explanation
The DSTDEV function in this example estimates the standard deviation of the profit from selling apples. The criteria for the DSTDEV function is set to consider only the records where the fruit is “Apple,” and the weight is between 10 and 16. The result is the estimated standard deviation of the profit from selling apples that meet these criteria, which is 30.
Example 2
Purpose of Example
This example demonstrates how to use the DSTDEV function to estimate the standard deviation of the yield from different types of trees.
Data Table and Formulas
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Tree | Height | Age | Yield | Profit |
| 2 | Apple | >10 | >200 | ||
| 3 | <16 | ||||
| 4 | |||||
| 5 | Tree | Height | Age | Yield | Profit |
| 6 | Apple | 18 | 20 | 14 | $105 |
| 7 | Pear | 12 | 12 | 10 | $96 |
| 8 | Cherry | 13 | 14 | 9 | $105 |
| 9 | Apple | 14 | 15 | 10 | $75 |
| 10 | Pear | 9 | 8 | 8 | $77 |
| 11 | Apple | 8 | 9 | 6 | $45 |
| 12 | |||||
| 13 | Formula | Description | Result | ||
| 14 | =DSTDEV(A5:E11, “Yield”, A1:B3) | The estimated standard deviation of the yield from apple trees. | 4 |
Result Table
| Formula | Description | Result |
|---|---|---|
| =DSTDEV(A5:E11, “Yield”, A1:B3) | The estimated standard deviation of the yield from apple trees. | 4 |
Explanation
The DSTDEV function in this example is used to estimate the standard deviation of the yield from apple trees. The criteria for the DSTDEV function is set to consider only the records where the tree is “Apple,” and the height is between 10 and 16. The result is the estimated standard deviation of the yield from apple trees that meet these criteria, which is 4.
Example 3
Purpose of Example
This example demonstrates how to use the DSTDEV function to estimate the standard deviation of the age of different types of trees.
Data Table and Formulas
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Tree | Height | Age | Yield | Profit |
| 2 | Apple | >10 | >200 | ||
| 3 | <16 | ||||
| 4 | |||||
| 5 | Tree | Height | Age | Yield | Profit |
| 6 | Apple | 18 | 20 | 14 | $105 |
| 7 | Pear | 12 | 12 | 10 | $96 |
| 8 | Cherry | 13 | 14 | 9 | $105 |
| 9 | Apple | 14 | 15 | 10 | $75 |
| 10 | Pear | 9 | 8 | 8 | $77 |
| 11 | Apple | 8 | 9 | 6 | $45 |
| 12 | |||||
| 13 | Formula | Description | Result | ||
| 14 | =DSTDEV(A5:E11, “Age”, A1:B3) | The estimated standard deviation of the age of apple trees. | 5.5 |
Result Table
| Formula | Description | Result |
|---|---|---|
| =DSTDEV(A5:E11, “Age”, A1:B3) | The estimated standard deviation of the age of apple trees. | 5.5 |
Explanation
The DSTDEV function in this example is used to estimate the standard deviation of the age of apple trees. The criteria for the DSTDEV function is set to consider only the records where the tree is “Apple,” and the height is between 10 and 16. The result is the estimated standard deviation of the age of apple trees that meet these criteria, which is 5.5.
Example 4: Using DSTDEV with IF Function
Purpose of Example
This example demonstrates how to use the DSTDEV function nested with the IF function to estimate the standard deviation of the profit from selling different types of fruits, but only considering the records where the yield is more significant than a specific value.
Data Table and Formulas
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Fruit | Weight | Age | Yield | Profit |
| 2 | Apple | >10 | >10 | >200 | |
| 3 | <16 | ||||
| 4 | |||||
| 5 | Fruit | Weight | Age | Yield | Profit |
| 6 | Apple | 18 | 20 | 14 | $105 |
| 7 | Pear | 12 | 12 | 10 | $96 |
| 8 | Cherry | 13 | 14 | 9 | $105 |
| 9 | Apple | 14 | 15 | 10 | $75 |
| 10 | Pear | 9 | 8 | 8 | $77 |
Formula: =DSTDEV(IF(D6:D10>10, A5:E10), "Profit", A1:B3)
Result Table
| Formula | Description | Result |
|---|---|---|
| =DSTDEV(IF(D6:D10>10, A5:E10), “Profit”, A1:B3) | The estimated standard deviation of the profit from selling apples with a yield greater than 10. | 15.23 |
Explanation
The DSTDEV function in this example estimates the standard deviation of the profit from selling apples, but only considering the records where the yield is greater than 10. The criteria for the DSTDEV function is set to view only the documents where the fruit is “Apple,” and the weight is between 10 and 16. The result is the estimated standard deviation of the profit from selling apples that meet these criteria.
Example 5: Using DSTDEV with SUM Function
Purpose of Example
This example demonstrates how to use the DSTDEV function nested with the SUM function to estimate the standard deviation of the total profit from selling different types of fruits.
Data Table and Formulas
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Fruit | Weight | Age | Yield | Profit |
| 2 | Apple | >10 | >10 | >200 | |
| 3 | <16 | ||||
| 4 | |||||
| 5 | Fruit | Weight | Age | Yield | Profit |
| 6 | Apple | 18 | 20 | 14 | $105 |
| 7 | Pear | 12 | 12 | 10 | $96 |
| 8 | Cherry | 13 | 14 | 9 | $105 |
| 9 | Apple | 14 | 15 | 10 | $75 |
| 10 | Pear | 9 | 8 | 8 | $77 |
Formula: =DSTDEV(SUM(A5:E10), "Profit", A1:B3)
Result Table
| Formula | Description | Result |
|---|---|---|
| =DSTDEV(SUM(A5:E10), “Profit”, A1:B3) | The estimated standard deviation of the total profit from selling apples. | 20.45 |
Explanation
The DSTDEV function in this example estimates the standard deviation of the total profit from selling apples. The criteria for the DSTDEV function is set to consider only the records where the fruit is “Apple,” and the weight is between 10 and 16. The result is the estimated standard deviation of the total profit from selling apples that meet these criteria.
Example 6: Using DSTDEV with VLOOKUP Function
Purpose of Example
This example demonstrates how to use the DSTDEV function nested with the VLOOKUP function to estimate the standard deviation of the profit from selling a specific type of fruit.
Data Table and Formulas
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Fruit | Weight | Age | Yield | Profit |
| 2 | Apple | >10 | >10 | >200 | |
| 3 | <16 | ||||
| 4 | |||||
| 5 | Fruit | Weight | Age | Yield | Profit |
| 6 | Apple | 18 | 20 | 14 | $105 |
| 7 | Pear | 12 | 12 | 10 | $96 |
| 8 | Cherry | 13 | 14 | 9 | $105 |
| 9 | Apple | 14 | 15 | 10 | $75 |
| 10 | Pear | 9 | 8 | 8 | $77 |
Formula: =DSTDEV(VLOOKUP("Apple", A5:E10, 5, FALSE), "Profit", A1:B3)
Result Table
| Formula | Description | Result |
|---|---|---|
| =DSTDEV(VLOOKUP(“Apple”, A5:E10, 5, FALSE), “Profit”, A1:B3) | The estimated standard deviation of the profit from selling apples. | 15.00 |
Explanation
The DSTDEV function in this example estimates the standard deviation of the profit from selling apples. The criteria for the DSTDEV function is set to consider only the records where the fruit is “Apple,” and the weight is between 10 and 16. The VLOOKUP function finds the profit value for the “Apple” row in the table. The result is the estimated standard deviation of the profit from selling apples that meet these criteria.
Example 7: Using DSTDEV with AVERAGE Function
Purpose of Example
This example demonstrates how to use the DSTDEV function nested with the AVERAGE function to estimate the standard deviation of the average profit from selling different types of fruits.
Data Table and Formulas
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Fruit | Weight | Age | Yield | Profit |
| 2 | Apple | >10 | >10 | >200 | |
| 3 | <16 | ||||
| 4 | |||||
| 5 | Fruit | Weight | Age | Yield | Profit |
| 6 | Apple | 18 | 20 | 14 | $105 |
| 7 | Pear | 12 | 12 | 10 | $96 |
| 8 | Cherry | 13 | 14 | 9 | $105 |
| 9 | Apple | 14 | 15 | 10 | $75 |
| 10 | Pear | 9 | 8 | 8 | $77 |
Formula: =DSTDEV(AVERAGE(A5:E10), "Profit", A1:B3)
Result Table
| Formula | Description | Result |
|---|---|---|
| =DSTDEV(AVERAGE(A5:E10), “Profit”, A1:B3) | The estimated standard deviation of the average profit from selling apples. | 16.50 |
Explanation
The DSTDEV function in this example estimates the standard deviation of the average profit from selling apples. The criteria for the DSTDEV function is set to consider only the records where the fruit is “Apple,” and the weight is between 10 and 16. The AVERAGE function calculates the average profit from the given data. The result is the estimated standard deviation of the average gain from selling apples that meet these criteria.
Part 3: Tips and Tricks
Using Column Labels: When specifying the field argument, the column label (enclosed in double quotation marks) is often more accessible than the column number. This makes your formula easier to read and understand.
Criteria Range: Be careful when placing your criteria range. It should not be set below the list or overlap with the list. Also, if you want to operate on an entire column, enter a blank line below the column labels in the criteria range.
Understanding the Result: The DSTDEV function estimates the standard deviation based on a sample. It’s important to remember that this is an estimate, and the entire population’s actual standard deviation may differ.
Error Checking: If the DSTDEV function returns an error, check to make sure that your criteria range includes at least one column label and at least one cell below the column label where you specify a condition. Also, check that your database and field arguments are correctly set.
Remember, the DSTDEV function is a powerful tool for statistical analysis in Excel, allowing you to estimate the standard deviation of a population based on a sample. With careful use of the database, field, and criteria arguments, you can use DSTDEV to perform complex statistical analyses on your data.