EXCEL
Database Functions
Range names
can also be used to define part of your worksheet as being a database, this then
gives you the opportunity to apply Excel’s powerful database functions to your
data.
In order to use a database functions
you need a list with column headings that contains your database and also a
separate range of cells containing the criteria you want to use to calculate
results from your data. We will
look at an example using a database of football goal scorers shown below. The whole of this list has
been given the range name ‘Football’.
In order to apply functions we will
have to set criteria for the data we want to look at.
By setting up your criteria cells correctly you can use AND and OR
operations when selecting your data as shown below.
 |
Using the criteria range
H1:J2 will find all records where the team is Chelsea AND the month
is August.
|
 |
Using
the criteria range H1:H3 will find all records where the team is
Chelsea OR
Man Utd.
|
 |
Using
the criteria range H1:I3 will find all
records where the team is Arsenal OR
the player is Nistelrooy.
|
Here are
some examples of database functions using the criteria shown above:
| =DMAX(Football,"Goals",H1:I3) |
Gives
the maximum number of goals scored by either an Arsenal player or
Nistelrooy. |
| =DMIN(Football,”Date”,H1:J2) |
Gives the minimum (earliest) date for
which there is a record for Chelsea in August. |
| =DSUM(Football,”Goals”,H1:J2) |
Gives the total number of goals scored
by Chelsea in August. |
| =DPRODUCT(Football,”Goals”,H1:H3) |
Gives the product of all the goals
scored by Chelsea or Man Utd (i.e. multiplies all the values together). |
| =DCOUNT(Football,”Goals”,H1:J2) |
Counts the number of records for
Chelsea in August where the Goals column contains a number. |
| =DCOUNTA(Football,”Goals”,H1:J2) |
Counts the number of records for
Chelsea in August where the Goals column is not blank – useful when some
cells may contain a text entry. |
| =DAVERAGE(Football,”Goals”,H1:J2) |
Calculates the average number of goals
scored by a player for Chelsea in August. |
| =DSTDEV(Football,”Goals”,H1:H3) |
Calculates the estimated standard
deviation of the number of goals scored by Chelsea and Man Utd players if
the data shown is only a sample of the entire league’s results. |
| =DSTDEVP(Football,”Goals”,H1:H3) |
Calculates the true standard deviation
of the number of goals scored by Chelsea and Man Utd players if the data
shown is the entire league’s results. |
| =DVAR(Football,”Goals”,H1:H3) |
Calculates the estimated variance in
the number of goals scored by Chelsea and Man Utd players if the data
shown is only a sample of the entire league’s results. |
| =DVARP(Football,”Goals”,H1:H3) |
Calculates the true variance in the
number of goals scored by Chelsea and Man Utd players if the data shown is
the entire league’s results. |
You can
use the DGET function to find a matching record in a database.
If we want to find the date of a match where a player managed to score 4
goals we could set up the criteria as shown below.
The formula =DGET(Football,”Date”,H1:H2)
would give the answer 16/4/04 if the cell were formatted as a date, the date
that Henry scored 4 goals for Arsenal. If
more than one record matches a DGET formula the result #NUM! is returned, if no
records match the error #VALUE! is returned.
It is also possible to set up
wildcards such as * and ? in criteria. Using
the expression =DMAX(Football,”Goals”,H1:H2) with the criteria set as
shown below would find the maximum number of goals scored for any team in a game.
The database functions will not work without an entry in the criteria
section so you sometimes need to use a wildcard to create criteria that select
all the data.
Click here to
return to the Excel index.
webmaster@allaboutoffice.co.uk |
