The `DSTDEVP` function in Excel is used to calculate the standard deviation of a population based on a dataset that matches specific criteria. It’s part of the database functions in Excel, which operate on a range of data organized as a table with rows and columns. Here’s how you can use the `DSTDEVP` function:

Syntax

DSTDEVP(database, field, criteria)

• database: This is the entire range of the data table, including the headers. It represents the whole dataset you are analyzing.
• field: This can be the name of the column (as a string enclosed in quotes) or the index number of the column in the dataset from which you wish to calculate the standard deviation.
• criteria: This is the range that specifies the condition(s) that the data must meet. This range should include at least one column label and the cells below the label should define the criteria.

Steps to Use `DSTDEVP`

• Prepare Your Data: Ensure your data is organized in a table format with headers. Each column should have a title in the first row.
• Define Criteria: Create a small area on your worksheet where you set up your criteria. The criteria range should include the same columns’ headers where you want the conditional filtering, and directly below, the criteria or condition you want to use.
• Formula: Enter the `DSTDEVP` formula at the desired cell location.

Example

Suppose you have a data range `A1:D10` that includes the following headers: “Region”, “Sales”, “Product”, and “Quantity”.

You want to calculate the standard deviation of “Quantity” for products where “Region” is “North”.

A1:  Region   B1: Sales   C1: Product   D1: Quantity
A2:  North    B2: 100     C2: A         D2: 20
A3:  South    B3: 150     C3: B         D3: 30
A4:  North    B4: 200     C4: C         D4: 40
A5:  East     B5: 180     C5: A         D5: 35
A6:  North    B6: 180     C6: B         D6: 25

Define criteria in a separate part of your worksheet, say in `F1:G2`:

F1: Region
F2: North

Now, use the `DSTDEVP` function:

=DSTDEVP(A1:D6, "Quantity", F1:F2)

This function calculates the standard deviation for the “Quantity” column where the “Region” is “North”.

Notes

• Use `DSTDEVP` if you believe the data range represents the entire population. If you are dealing with a sample and not the entire population, use `DSTDEV` instead.
• Make sure your criteria range includes headers that precisely match those in your database range. Mismatch will lead to errors or undesired results.

Follow us on Social Media

facebooklinkedin

Unlock Your Potential

Excel

Access

Power BI

Help us grow the project