The DSTDEV function is one of the database functions in Microsoft Excel. You might be asking yourself how a function in Excel is being called a database function when Excel is only a spreadsheet and not a database, such as Microsoft Access. We’ll see that in a moment.
As the name suggests DSTDEV or Database Standard Deviation computes the standard deviation from a range of numerical values. This would just be a simple definition of the function. DSTDEV can do more complex searches based on criteria we provide. Let us study DSTDEV in more detail.
Before we move into the usage of DSTDEV, a few words on Standard Deviation would add to the clarity of our article. Standard Deviation is used in Statistics and Probability to express a deviation from an average of numerical values. Suppose the average age of the population in a state is 35 and the standard deviation is high (a high figure) denotes that there is a big difference in the ages of the population whereas a small standard deviation (a small figure) indicates that the average age is near and around 35.
For our example if we want to find out the standard deviation of sales from a table of data we can use DSTDEV. We can also use additional criteria such as finding out the standard deviation of sales for the state of Michigan and for the month of May etc.,
Like all other Microsoft Excel database functions, to use DSTDEV, there are certain pre-requisites. Do you recall just a while ago we mentioned that DSTDEV is a database function? So where is our database? Ok, here goes. We actually don’t use a Database but we present the data inside the spreadsheet in the form of a database. Didn’t get it? Read on. So what does a database typically have? A database has fields and records. A record is a group of fields. So all we need to do in Excel is to present this data in the form of fields and records. Many of you have actually been doing this ever since you used Excel but were unaware of it. Remember your columns and column headers in your spread sheet. These become the fields. A row in your spreadsheet becomes the record. But to use these columns and rows with DSTDEV we’ll have to remember some fundamental and mandatory rules without which DSTDEV will not function properly.
- Your columns must have headers.
- No empty or blank cells are allowed. So all cells MUST have data in them. ABSOLUTELY no blank rows either.
- There must not be data in cells adjacent i.e. touching the cells we want to include in the database.
The syntax of the DSTDEV function is as follows – DSTDEV(database, field, criteria). Let’s explore the parameters one by one.
Database – This is what we have been discussing so far. Let us assume we have the following fields i.e. column headers Salesperson, State, Month(Date), Sales and Grade
Field – Is the field or column where we want to determine the standard deviation, in our case the Sales field.
Criteria – Now here is where you can add other conditions, one or more. To specify criteria we have to do some additional steps. In an unused portion of your spreadsheet create an identical table, this time with two (for clarity) row cells Field Name and Criteria, exactly one on top of the other. This is a mandatory condition. So the row of headers will contain the following headers, Field Name, Salesperson, State, Month(Date) Sales and Grade. You will fill in the cell just below Field Name with the word Criteria. This table must not be touching any cells of the main database table.
We’ll now look at some examples of DSTDEV computations using criteria.
First let us name the database here so that we may just have to refer to it by name instead of selecting it each time. To name the database select all the fields and all the rows (Ctrl+down arrow). Next name the selection by entering name in the Name field (top left) and this becomes the name of our database. Let’s say our database is named, Sales1.
So at the cell where we want to add the DSTDEV function enter =DSTDEV(Sales1,
Next, click on the cell header of the column where you are going to compute the standard deviation. In our example we click on the Sales column header. The cell is automatically inserted into formula. Alternatively you can also enter the name of the column header as “Sales” or double click or use F9.
Next we need to input the criteria. For criteria we need to click on the field we wish to use in the criteria. In our example we wish to compute the standard deviation of sales in the state of Michigan and so we enter “Michigan” in the cell below the header cell that is titled State in the separate data table we created for field and criteria. Now click the field State and drag in the value for the field for criteria also from the Criteria row where we had entered “Michigan”.
The DSTDEV function will now have the criteria data embedded inside the formula and the standard deviation is computed and displayed in the cell.
The final formula will look something like this – =DSTDEV(Sales1, “Sales”, I14:I15)
Note that as we discussed earlier you can have more than one criterion. Suppose you also want to find the standard deviation of sales in Michigan for the month on May. Under Month(Date) in the criteria table enter May. This time when you click and drag cells for criteria, drag in the State field and the “Michigan” and the Month(Date) and “May” criteria too. This was a “AND” condition i.e. one where we added two conditions. Now suppose you wanted an “OR” condition place the second condition below the first condition, i.e. vertically and not horizontally, and then drag them both.
The result, i.e. the standard deviation of sales in the state of Michigan for the month of May will be instantly reflected in the cell.
Note that there is yet another method of inserting the DSTDEV function into the spreadsheet. For this first select the cell where you would like to insert the function. Then from the Formula tab, the Function Library group, choose Insert Function. The Insert Function dialog opens. Here select the DSTDEV function from the Select a Function box. Note that you need to ensure you have selected the Database category located just above the Select a Function box. A new Function Arguments dialog is displayed where you can select the Database, Field and Criteria by clicking the buttons (located adjacent to the corresponding fields) which will take the control back to your spreadsheet and back to the dialog by inserting the selected ranges into the fields. When done click OK and your function is all set. As a confirmation your cell will now have the value computed by DSTDEV inside. That is it. And if you liked this free Excel help article, there are more, and you do not need to be an Excel consultant to use them, any business, small or large can apply what you will learn in these free articles.