Microsoft Excel contains and number of database

functions that are available and will assist you in extracting information from your database.

In this segment you will learn how to find and activate the function, 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.