Click here for great value web hosting.

ALL ABOUT OFFICE

HOME

ACCESS

EXCEL

WORD

OUTLOOK

POWERPOINT




Search for
 

help


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