When you are recording data in Excel, it is sometimes required to calculate some basic statistical parameters such as mean, standard deviation and variance to study the numerical characteristics of a population. Standard Deviation gives information about the scattering of data, which is most commonly used to get an idea about the statistical significance of a value. In Excel, the DSTDVEP function is used to calculate to find the standard deviation of a data.
Excel DSTDEVP Function
Excel DSTDEVP function returns the standard deviation for an entire population of data. If the recorded data, represents the entire population then DSTDEVP function is used.
=DSTDEVP(database, field, criteria)
Parameters of DSTDEVP function
Database (required): Range of the database including headers.
Field (required): Index to count or also known as Field name.
Criteria (required): Range of the criteria including headers.
Consider the following example, in which the temperature record of two cities, i.e., New York and California have been recorded for 3 days. In order to calculate the standard deviation of the temperature for New York, the following procedure must be followed.
Figure1. Example Sheet
If you want to calculate the standard deviation of the temperature of New York, then you can use the DSTDEVP formula in the formula bar. The general syntax for this function is depicted above. Now copy the field names and the first row at one place to make it specific for defining the criteria.
Figure2. Specifying the criteria for calculating standard deviation using DSTDEVP function in Excel
Now click on the F5 cell and enter the DSTDEVP formula for this in the formula bar, following the syntax. For this example the database is present in the cells B2:C8 and the criteria have been defined in E2:F3 cells. The field for calculation is Temperature.
The formula entered for this example is as follows:
Figure3. Using the DSTDEVP function in Excel
To calculate the standard deviation for both the cities following formula can be used:
Figure4. Setting the Data validation to allow numbers only
Note on the usage of DSTDEVP function in Excel:
- The field parameter in DSTDEVP function can be defined in two ways. Either by field name enclosed in double quotes or by field index. For example, in the above example, there are 2 fields, i.e., Name of city and temperature having index numbers 1 and 2 respectively.
- DSTDEVP supports wildcards in the criteria.
- Criteria can include one or multiple rows.
- Database and criteria range must include matched headers.
Still need some help with Excel formatting or have other questions about Excel? Connect with a live Excel expert here for some 1 on 1 help. Your first session is always free.