The DSTDEVP function in Google Sheets is designed to compute the standard deviation of a complete population based on data derived from a specified range in a manner reminiscent of SQL queries. This powerful function is particularly helpful in statistical analysis, allowing users to evaluate the variability or dispersion in a set of values with precision.
Syntax
DSTDEVP(database, field, criteria)
- database: The range or array containing the data you wish to analyze.
- field: The column in the range from which to extract the values; this can be specified by either a number or a column label.
- criteria: The condition that determines which records from the database to include in the calculation.
Example #1
DSTDEVP(A1:C10, 2, "=1")
This function call calculates the standard deviation of all values in column 2 of the specified database (A1:C10) where the first column equals 1. For instance, if the relevant data points are 10, 12, and 14, the result would be 2.00.
Example #2
DSTDEVP(A1:C10, "Revenue", "<2000")
This example computes the standard deviation for the 'Revenue' column in the specified range, considering only those records with revenue less than 2000. If the applicable revenue values are 900, 1500, and 1800, the result would be approximately 300.00.
Example #3
DSTDEVP(A1:C10, 1, "=2")
This command will calculate the standard deviation for values in column 1 of the specified database where the second column equals 2. If the related values are 5, 7, and 9, the resulting standard deviation would be approximately 2.00.
Error handling
- DIV/0!: Occurs when there are no valid records matching the criteria; essentially, the function cannot compute a standard deviation without data.
- VALUE!: This error arises when the specified field does not contain numeric values, making it impossible to calculate a standard deviation.
- NAME?: This indicates that the function name was misspelled or incorrectly referenced, which prevents successful execution.