Home // Video Tutorials // 3 - 6 (Intermediate) // 5 // Excel Database Functions e.g. DSUM, DCOUNT, DAVERAGE

Excel Database Functions e.g. DSUM, DCOUNT, DAVERAGE

How to use the Excel Database Functions such as DSUM, DCOUNT, DAVERAGE.

Excel Database Functions

In this segment you will learn how to find and activate the Excel database functions, set up spreadsheets to facilitate using the database functions, and using these functions.

Here we have a simple database where we have some information about trees, the name, its height, age, yield and profit and what we want to do is extract some information, so count how many trees  meet certain conditions, add up certain information depending on some conditions. The important thing about database functions is that it allows you to specify the criteria within the spreadsheet itself.

So just to give you an example, what we need to do is take the headings that we have here and copy them and paste them here. And now what we can do is specify certain criteria t pull through. So lets say I want to look at apple trees and I want the height lets say  to be bigger then 10. This will now allow you to use functions to use these criteria to pull information out of this database.

Lets try a simple function first for now. We just want to count how many  of the trees here are apple trees which are bigger then 10. SO if you click on the function wizard, and you go to the database section, you will see that there is one called DCOUNT. When I click OK, the first criteria is where is the database. So we just highlight the entire database and you make it absolute by clicking the F4 key.  It then asks for the field. And you can see it says a field is either the label of the column in double quotation marks, or a number that represents the columns position in that list. In this case I am going to use “height”, so I put double quotes, height. Now the criteria it says is the range of cells that contain the conditions you specify. The range includes a column label and one cell below the label for a condition. So in that case I want to highlight this section over there and what we are saying is look at these conditions and apply that to the database. So when I say OK you will see it tells me 2, which when you look here, here’s a apple tree but it is 8 so it does not meet the one condition, here’s an apple 14,  here’s an apple 18, so there are 2 trees that meet these conditions.

Now that you have this formula set up, it is quite convenient, because this at the moment looks a those conditions, but lets say I go here, and change that to pear, when I click ENTER, you will see that that goes to one, because there is only one tree that is a pear tree and is bigger than the 10. Similarly we could go here, and change that to be bigger than 8, and that will change to 2. So you’ve got a function that goes through the database, and checks to certain criteria that exists in the spreadsheet and gives you the result.

Now lets introduce another complexity. Lets say we want to know how many pear trees are bigger then 8, and we want to know how many apple trees are bigger then 10. In order to make this function work, we click on FX, we will DCOUNT them, again we specify the database, the field in this case instead of typing, “height” I can also say 2, which is the second column, and now for the criteria, I can highlight all of these and say OK. Now what we will see is it gives me an answer of 4, and that’s because there are 2 pear trees bigger then 8 and there are 2 apple trees bigger then 10. The trick to understanding what this is doing is that anything that is included along the row is the equivalent of an AND function ad anything that is included down a column is the equivalent of an OR function. So you will see here, we are saying it is a pear tree AND it must be bigger then 8. If we go down, we are saying it can be a pear tree OR an apple tree. So for example, we can actually introduce another condition. Lets say we wanted it to be a pear tree bigger then 8 but less than 11. I can then come here, type the word height  and put the function less then 11. So now when I do the function you will see I highlight the whole section and what it is going to do is look here a say it must be pear AND bigger then 8 AND less then 11OR an apple that  also bigger then 10 . So lets just do that function, again the database, the field is number 2, the criteria, in this case I am going to highlight the entire area and when I say OK you will see it gives me 3 now. And the reason it gives me 3 is because there is 1 pear tree that bigger then 8 but less then 11 because this one does not match, and the apple bigger then 10 is 1, 2, hence the answer is 3.

So it is incredibly important to remember that you can specify AND type criteria along the rows, and OR type criteria down the columns, and you can duplicate the columns if there is more criteria you want to put in.

We can now work though some of the other database functions, and it will hopefully show you how this all works. Lets say we want to find out which tree between apple and pear have the maximum profit. Click on a cell, activate the function wizard, and go to the database functions, to the DMAX function. Again we need to identify the database, specify the field in that case it will be profit so click that and the criteria I want to use here is just pear and apple. So I’m going to just highlight this section, so look either pear or apple. When I say OK, you will see it gives me a number of 105 which is the maximum profit between pear and apple trees.

Another useful function is the DSUM function. What we want to know is what is the total profit for the pear trees. So I click on a cell, activate the function wizard, find DSUM, say OK, show it where the database is, tell it what field we are looking at, in this case it is the profit field,  and specify the criteria and in this case I just highlight these 2,  and when I say OK it gives us 172.8, which is the total of that plus that.

There are other database functions s just to give you an idea, we can have a quick look through here. They all use the same criteria, whatever the function is you show where the database is, you specify the field,   either with a number or the name, and then you give the criteria in this sort of format. So using DAVERAGE you can get an average of a database selection, you can count all the numbers, you can count the non blanks, you can DGET particular information out of a database, you can get the maximum number, the minimum number, you can get it to multiple all the numbers it finds, you can get a standard deviation based on a sample or a standard deviation based on the entire population. We have been through the DSUM, you can get a variance and the  variance based on a population.

So using these functions you can extract all sorts of useful information out of a database.