In this article, we will learn about how to use the DSTDEVP function in Excel.
DSTDEVP function is built-in database function in Excel. The DSTDEVP function returns the Standard deviation of the values from the entire population database having multiple conditions. Conditions to the function is given using a table, which must have the same fields or headers as on the database.
DSTDEVP function returns the Standard deviation of the field based on given multiple criteria. The DSTDEVP function takes criteria as a table having the same fields as on the database.
Syntax: =DSTDEVP(database, field, criteria)
The DSTDEVP function syntax has the following arguments:
- Database Required. 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 Required. Indicates which column is used in the function. 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 Required. The range of cells that contains the conditions that 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 in which you specify a condition for the column.
Example: Let’s look at some Excel DSTDEVP function examples and explore how to use the DSTDEVP function as a worksheet function in Microsoft Excel:
Here we have a list of Products bought with their respective details & We need to get the Standard deviation from the TotalPrice based on criterias mentioned in the table.
First condition is City : CALIFORNIA
Second condition is Product : Carrot
So the function must return the Standard deviation of the Carrot (Product) bought from CALIFORNIA (city)
Use the formula for the above condition:
Syntax: =DSTDEVP(A1:F17,”TotalPrice”,A20:F21)
Result:
DSTDEV function can have multiple criteria on one field by adding rows in the table
Now we will take an example taking multiple criteria on the same database.
Here we have multiple conditions on the field Quantity i.e. less than 100 & greater than 50 units bought from FLORIDA. We need to get the Standard deviation of TotalPrice having the conditions stated in the snapshot.
Use the formula for the above condition:
Syntax: =DSTDEVP(A1:F17,”TotalPrice”,A20:F22)
Result:
Note:
- DSTDEVP is mean to calculate variance for an entire population. If data represents a sample, use the DSTDEV function.
- DSTDEVP supports wildcards in criteria.
- Criteria can include more than one row (as explained above).
- The field argument can be supplied as a name in double quotes (“”) or as a number representing field index.
- The database and criteria ranges must include matching headers.
