How to use the DSTDEVP function
What is the DSTDEVP function?
The DSTDEVP function calculates the standard deviation based on a population. The function also allows you to specify criteria applied to your data.
Table of Contents
1. Introduction
What is DSTDEVP an abbreviation of?
DSTDEVP is an abbreviation of database standard deviation population.
What is Standard Deviation?
Standard deviation tells you how far from the average values are spread out. Both charts above have numbers and an average plotted, they share the exact same average however, the numbers are not the same.
Chart A above shows that the values are more spread out than the values in chart B. Chart A has a standard deviation of 23.45256334, standard deviation for chart B is 5.207075606. Standard deviation is fundamental in statistics.
A normal distribution is a symmetric, bell-shaped probability distribution that is commonly used in statistics and probability theory. The shape of the normal distribution is determined by its mean (μ) and standard deviation (σ). The mean represents the center of the distribution, while the standard deviation represents the spread or dispersion of the data around the mean.
- A normal distribution with a standard deviation of 0.5 is relatively narrow and tightly clustered around the mean.
The values in the distribution are concentrated within a smaller range, with most values falling closer to the mean. The curve appears tall and steep, indicating a higher concentration of data points near the mean. - A normal distribution with a standard deviation of 1 is the most commonly used normal distribution, often referred to as the standard normal distribution.
The standard deviation of 1 represents a moderate spread of the data around the mean. About 68% of the data falls within one standard deviation (±1σ) of the mean, and approximately 95% of the data falls within two standard deviations (±2σ) of the mean. The curve has a characteristic bell shape, with a smooth and gradual taper towards the tails. - A normal distribution with a standard deviation of 2 is relatively wide and spread out compared to the standard normal distribution. The data is dispersed over a larger range, with values more spread out from the mean. The curve appears shorter and flatter, indicating a lower concentration of data points near the mean.
Does the DSTDEVP function count blank cells, boolean, and text values?
No, blank cells, boolean values and text values are not counted.
Does the DSTDEVP function ignore error values?
No, it doesn't. However, error values are ignored if they are filtered out by the criteria argument.
What is a database in this context?
Excel defines a database as 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.
Why use the DSTDEVP function?
The DSTDEVP function calculates the standard deviation of cells containing numbers that match a condition or criteria in a list/database whereas the STDEVP function calculates the standard deviation without a condition/criteria.
Where can you place the criteria range?
You can place your criteria range wherever you want on your worksheet, however, it is not recommended below the list/database. The function needs a blank row below the list to work properly.
What criteria characters are allowed?
Allowed criteria range characters are less than and greater than signs <>, use them to specify a criteria range. Also, asterisks * can be used to match partial strings.
How to calculate the standard deviation for the entire list/database?
To include the entire list/database enter a blank line below the criteria range column labels.
2. Syntax
DSTDEVP(database, field, criteria)
| database | Required. The cell reference to a list or database. |
| field | Required. The field argument lets you choose which column to use. You can use the column name enclosed with double quotation marks or the corresponding column number. |
| criteria | Required. A cell reference to the criteria range. The criteria range needs to have column labels and at least one condition below the column label. |
3. Example 1
You have a dataset containing the widths of a product manufactured by a company. Calculate the population standard deviation of the width values to understand the spread of the manufactured products? There are three categories, Large, Small and Medium. Find the standard deviation of products in category "Large".
The data is in cell range B15:D16 in the image above, here are they:
| Item | Size | Number |
| A102 | M | 370 |
| A103 | L | 690 |
| A099 | S | 310 |
| A412 | S | 190 |
| A341 | L | 550 |
| A340 | M | 730 |
| A202 | L | 623 |
The arguments are:
- database = B18:D25
- field = 3 (third column)
- criteria = B15:C16
Formula in cell B28:
The formula returns 57.17 which represents the standard deviation based on a population. The math formula behind is:
DSTDEVP function = √(Σ(x - x̄)2/n)
√ - square root
Σ - sum of
x̄ - arithmetic mean
n - count of values
The values that match the condition "L" in cell range B18:D25 are 690, 550, and 623. The mean is 690 + 550 + 623 equals 1863. 1863 / 3 equals 621.
690 - 621 = 69
550 - 621 = -71
623 - 621 = 2
The square of these values are:
692 = 4761
(-71)2 = 5041
22 = 4
The total is 4761 + 5041 + 4 = 9806
9806/3 = 3268.6666667
√3268.6666667 = 57.17 which is the same value in cell B28.
4. Example 2
A researcher studying a company has compiled data on the lifespan of a certain machine. Assume the data follows a normal distribution. Calculate the standard deviation from numbers in D19:D25, based on "Items" in B19:B25 that begin with "A"?
The data is in cell range B15:D16 in the image above, here are they:
| Item | Size | Number |
| B102 | M | 370 |
| A103 | L | 690 |
| B099 | S | 310 |
| A412 | S | 190 |
| A341 | L | 550 |
| B340 | M | 730 |
| A202 | L | 623 |
The arguments are:
- database = B18:D25
- field = 3 (third column)
- criteria = B15:C16
Cell B16 contains "A*" meaning that all values that begins with A are valid. The asterisk matches 0 (zero) to any number of characters.
Formula in cell B28:
The formula returns 193.08 which represents the standard deviation based on a population. The math formula behind is:
DSTDEVP function = √(Σ(x - x̄)2/n)
√ - square root
Σ - sum of
x̄ - arithmetic mean
n - count of values
The values that match the condition "A*" in cell range B18:D25 are 690, 190, 550, and 623. The mean is 690 + 190 + 550 + 623 equals 2053. 2053 / 4 equals 513.25
690 - 513.25 = 176.75
190 - 513.25 = -323.25
550 - 513.25 = 36.75
623 - 513.25 = 109.75
The square of these values are:
176.752 = 31240.5625
(-323.25)2 = 104490.5625
36.752 = 1350.5625
109.752 = 12045.0625
The total is 31240.5625 + 104490.5625 + 1350.5625 + 12045.0625 = 149126.75
149126.75/4 = 37,281.6875
√37,281.6875 = 193.08 which is the same value in cell B28.
Functions in 'Database' category
The DSTDEVP function function is one of 11 functions in the 'Database' category.
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Contact Oscar
You can contact me through this contact form