9. Database Functions
The relatively unknown database functions are great for quickly looking up multiple criteria. Learn how to use them in this lesson.
- A great solution for performing quick lookups with multiple criteria
Sample database function
DSUM(database, field, criteria)
--- database: Select all the columns in your dataset
--- field: The column header you wish to perform sum on
--- criteria: Array of cells encompassing the sales headers and criteria
Other database functions:
- DSTDEV (Standard deviation for population sample)
- DSTDEVP (Standard deviation for a population)
- DVAR (Variance for a population sample)
- DVARP (Variance for a population)
Data base functions are a little known set of commands in Excel that allow you to perform very powerful lookups on your data sets.
They’re not as flexible as INDEX, MATCH or VLOOKUP, but they are a great solution if you want to perform single lookups on multiple criteria.
There are 12 database functions in all, but the 5 most useful are listed in our search panel.
Let's start off by looking at DSUM to calculate the total revenue generated by the sale rep Baines.
So first I'll write the name Baines underneath Sales person.
And then we'll create our DSUM function.
So I'll write “=dsum” and the first item we must enter is our database, which is our full data set, including the headers.
I’ve a named range for this called SALES_TRANSACTIONS, so I'll include that here.
Next I need to include the field, and the field will be revenue.
And lastly I have to enter the criteria, which are these six cells.
I currently have three criteria: revenue, sales person and payment date, but I can include as many criteria as I want.
I’ll then close the bracket and press Enter to finish.
And this tells me the total revenue contributed by Baines.
Now let's add some more criteria starting with the payment date.
So let's say we want to find out the revenue Baines contributed in the first half of the year.
So we’ll say the payment date is less than the first of July, 2012.
And as you can see, the DSUM value updates accordingly.
Now let's add another criterion.
Say we only want to include the transactions above $20 000 in value.
So we’ll say greater than $20,000, and again, you can see that the DSUM figure updates accordingly.
Let's now move onto DCOUNT.
So I'll write ”=dcount”, again I’m asked for the database, which is sales transactions.
The field can actually be anyone of the headers, so I'll select the same one, and the criteria are the same six cells as before.
I'll close the bracket and press Enter to complete.
And this tells me that Baines sold five transactions in the first half of the year above $20 000 in value.
Hopefully, it’s becoming clear that the database functions are very powerful lookup tools, particularly if you have multiple criteria.
Ising the same inputs for DSUM and DCOUNT, you can also calculate DAVERAGE, DMAX and DMIN for your data set.
I'm going to leave these three commands as an exercise for you to complete.
Before I wrap up this lesson, it’s important to mention a few requirements for using database functions.
First, the data set in question must be arranged with the sales headers across the top, and the data arranged in rows underneath.
Second, the headings and the fields must replicate exactly the spelling in the database headers, otherwise the functions won't work.
However once you have both of these requirements in place, the database functions are all yours to use.