How to use the DSTDEV function

What is the DSTDEV function?

The DSTDEV function calculates an estimation of the standard deviation based on a sample of a population. The function allows you to specify criteria applied to a population/database.

1. Introduction

What is DSTDEV an abbreviation of?

DSTDEV is an abbreviation of Database Standard Deviation.

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 DSTDEV function?

The DSTDEV function multiplies numbers that match a condition or criteria in a list/database whereas the STDEV.S function performs a calculation 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.

What is a STDEV?

STDEV stands for standard deviation. It  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.

What is the difference between the DSTDEV function and the DSTDEVP function?

DSTDEVP function calculates the standard deviation for a population and the DSTDEV function calculates the standard deviation for a sample. DSTDEVP uses the count of all values (n) in the denominator.

DSTDEV uses (n-1) in the denominator (Bessel's correction). This accounts for the difference between sample variance and population variance in statistics. DSTDEV is better for sample inferential statistics.

DSTDEVP math formula:

DSTDEV math formula:

When to use the DSTDEVP function and the DSTDEV function?

Use DSTDEVP if you have the full population data. Use DSTDEV if you have a sample of limited data from a larger population. DSTDEVP will result in a lower standard deviation compared to DSTDEV on the same data.

Sample standard deviation is considered a better estimate for inferring population parameters.

What is inferring population parameters?

Population parameters refer to the actual values of statistics that describe an entire population, such as the population mean or standard deviation. However, the true population parameter values are often not known.

What is sample inferential statistics?

Sample inferential statistics are methods that allow using statistics calculated on a sample of data to infer the unknown population parameters.

For example:

• The sample mean can be used to estimate the population mean.
• The sample standard deviation can estimate the population standard deviation.

2. Syntax

DSTDEV(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

This is an Excel function that calculates the standard deviation of a specific column (field) within a data range based on provided criteria.

The formula in cell B15 is

Arguments:

1. B18:C25 - This is a range reference that contains the data for the calculation. It includes the Item, Size and Number columns from rows 5 to 12.
2. 3 - This is a number that specifies the data field for which the standard deviation is to be calculated. In this case, 3 refers to the Number column within the data range.
3. B3:C3 - This is another range reference that contains the criteria for the calculation. In this case, it includes the value "S" in cell C3, which is the criteria for the Size column.

The data in the referenced ranges:
- B18:C25 contains a list of items with their sizes and associated numbers.

Item	Size	Number
A102	M	370
A103	L	690
A099	S	310
A412	S	190
A341	L	550
A340	M	730
A202	S	623

- 3 represents the field number. B5:C12 contains three columns, the formula calculates the standard deviation based on the numbers in the third column.
- B15:D16 includes the value "S" in cell C16, which is the criteria for the calculation.

The DSTDEV function calculates the standard deviation of the Number values for those rows where the Size value matches the criteria "S".  The result 223.55538712 is obtained by calculating the standard deviation of the numbers for items with size "S" in the data range. These items are A099, A412, and A202, with numbers 310, 190, and 623, respectively.

The image above shows a normal distribution (blue curve) with a mean of 374 and a standard deviation of 224. The mean is the center of the blue curve and σ represents the standard deviation.

Mean = 374
Std dev = 224
1σ = 598
2σ = 821
3σ =  1045
-1σ = 151
-2σ = -73
-3σ =  -296

The math formula behind the calculation in cell B15 is:

√(Σ(x-x̄)²/(n-1))

Σ - sum
x̄ - the mean
x - all x values
n - number of x values
√ - square root
² - square

To calculate the standard deviation from a sample based on the following observations 310, 190, and 623:

1. Find the mean (x̄) of the observations:
   x̄ = (310 + 190 + 623) / 3
   x̄ = 1123 / 3
   x̄ = 374.33
2. Calculate the deviation of each observation from the mean (x - x̄):
   310 - 374.33 = -64.33
   190 - 374.33 = -184.33
   623 - 374.33 = 248.67
3. Square each deviation:
   (-64.33)^2 = 4138.45
   (-184.33)^2 = 33973.89
   (248.67)^2 = 61838.45
4. Sum the squared deviations:
   4138.45 + 33973.89 + 61838.45 = 99950.79
5. Divide the sum of squared deviations by (n - 1), where n is the number of observations:
   99950.79 / (3 - 1) = 99950.79 / 2 = 49975.395
6. Take the square root of the result from step 5:
   √49975.395 = 223.55538712

The standard deviation of the sample observations 310, 190, and 623 is 223.55538712, which matches the result shown in the image.

Functions in 'Database' category

The DSTDEV function function is one of 11 functions in the 'Database' category.

Excel function categories

Excel categories

Leave a Comment