This post explains how to use the DSTDEVP function in Google Sheets to calculate the standard deviation of an entire population.
When calculating standard deviation in Google Sheets, functions are categorized into two types: Database and Statistical.
Database functions allow you to apply calculations on selected entries in a dataset, whereas Statistical functions do not. For statistical calculations, you may need to use functions like QUERY or FILTER to apply criteria, which is where database functions excel.
The DSTDEVP function in Google Sheets belongs to the database category. The key point to remember is that these functions require a well-formatted dataset.
A well-formatted dataset is a multi-column array or range containing field labels (column names) in the topmost row. Below is an example of such a dataset.
Introduction
Before diving into the function syntax and formula examples, let’s grasp the basics of standard deviation calculations. Standard deviation is a crucial statistical measure that helps us understand the variability in a dataset. By calculating the standard deviation of a dataset, we can assess how spread out the values are around the mean (average).
Consider the highlighted records in the table above. Here’s how to calculate the standard deviation of the values in the “Price” column without using the DSTDEVP function:
- Calculate the Mean:
=(60+90+80+75)/4// returns 76.25 - Subtract the Mean and Square the Result:
=(60-76.25)^2+(90-76.25)^2+(80-76.25)^2+(75-76.25)^2// returns 468.75 - Divide by the Number of Data Points:
=((60-76.25)^2+(90-76.25)^2+(80-76.25)^2+(75-76.25)^2)/4// returns 117.1875 - Take the Square Root: 10.825 [
=SQRT(117.1875)].
Understanding standard deviation is beneficial for various reasons. It helps in identifying trends and patterns within data, allowing for better decision-making.
Syntax of the DSTDEVP Function in Google Sheets
Syntax:
DSTDEVP(database, field, criteria)Arguments:
- database: The structured data or well-formatted dataset, as explained earlier.
- field: The column index number that contains the value to calculate. You can either use the corresponding field label (in double quotation marks) or the column index number (the relative position of the calculation column in the database).
- criteria: Specifies which records to include in the calculation. This must be entered as an array and must contain at least one field label and one cell below it.
Formula Examples
Like other database functions, you can provide criteria as a range reference or hard-code them within the formula.
Example 1: One Criterion
=DSTDEVP(C2:E18, "Price", G3:G4)Where:
- database:
C2:E18 - field:
"Price" - criteria:
G3:G4
Example 2: Two Criteria
=DSTDEVP(C2:E18, "Price", G7:H8)In this example, the database and field remain the same since we are using the same table. However, the criteria range differs, as it is set to G7:H8.
Hard-Coding Criteria in the DSTDEVP Function
Google Sheets database functions are flexible regarding the use of conditions. In the examples above, the criteria were specified as an external array. Here’s how to hard-code them within the formula:
Example 1: Hard-Coded Criterion
=DSTDEVP(C2:E18, "Price", {"Item"; "Apple"})Example 2: Hard-Coded with Two Criteria
=DSTDEVP(C2:E18, "Price", {"City", "Item"; "B", "Apple"})If you want to omit filtering and not include any criteria, remember that all arguments are mandatory. To achieve this, you can use two empty cells in the criteria section, such as {IF(,,); IF(,,)}. This allows you to satisfy the function’s requirements without applying any specific conditions.
