This browser does not support basic Web standards, preventing the display of our site's intended design. May we suggest that you upgrade your browser?

Microsoft Excel 2003/2004

Analyzing Your Database with Functions

Database functions can make your worksheet more useful by extracting information about the data in a form that will answer a question or help in deciding the best way to spend your resources. Whether in your office, school, or preparation for an important meeting, database functions can be an important way to analyze your information. For information on subtotals, refer to Calculating Subtotals in Databases.

return to topDefining the Syntax of Database Functions

All database functions use the same format for calculations. The format is the following:

=function(database,field,criteria)

Database
Refers to the range of cells that make up the database. This includes the row with the field names identifying the type of information in each column.

Field
Indicates which field will be analyzed and used in the function. The field can be referred to as the position number of the column or the field label within quotes.

Criteria
Criteria is the range of cells containing the conditions by which Excel will identify records to be evaluated to complete the function. Criteria must have a column label and at least one condition in the cell below its range. For more information, refer to Establishing Criteria.

return to topPerforming Database Functions

Before performing a database function, you must create the criteria for the function. If you have not already established your criteria, refer to Establishing Criteria.

Windows:

  1. Place the cursor in the cell where you want the results of the function to appear

  2. On the Formula Bar, click INSERT FUNCTIONInsert Function button
    The Insert Function dialog box appears.
    Insert Function dialog box

  3. From the Or select a category pull-down list, select Database

  4. From the Select a function scroll list, select the appropriate function

  5. Click OK
    The Function Arguments dialog box appears.

  6. To complete the Database, Field, and Criteria text boxes,
    NOTE: For a definition of these terms, refer to Defining the Syntax of Database Functions.
    1. Select the appropriate text box
    2. Click COLLAPSE DIALOGCollapse Dialog button
    3. Select the data range
    4. Click RESTORE DIALOGRestore Dialog button

    5. Repeat steps a-d until all text boxes are completed

  7. To perform the function, click OK

Macintosh:

  1. Place the cursor in the cell where you want the results of the function to appear

  2. On the Standard toolbar, click PASTE FUNCTIONPaste Function button
    The Paste Function dialog box appears.
    Paste Function dialog box

  3. From the Function Category list, select Database

  4. From the Function name scroll list, select the appropriate function
    NOTE: A description of the selected function appears below the lists.

  5. Click OK
    The respective dialog box appears.

  6. To complete the Database, Field, and Criteria text boxes,
    NOTE: For a definition of these terms, refer to Defining the Syntax of Database Functions.
    1. Select the appropriate text box
    2. Click COLLAPSE DIALOGCollapse Dialog button
    3. Select the data range
    4. Click RESTORE DIALOGRestore Dialog button

    5. Repeat steps a-d until all text boxes are completed

  7. To perform the function, click OK

return to topExamples of Database Functions

For more information on how to perform any of these functions, refer to Performing a Database Function, which provides information on the Insert Function dialog box. The table in this section summarizes the most commonly used database functions and is based on the following Excel database:

database example table

DSUM: Adds the numbers in the field column of records in the database that match the criteria.
Syntax =DSUM(database, field, criteria)
Example To calculate the total amount that Chris was paid, type the following function:
=DSUM(C3:G15,G3,C31:C32) field as cell reference
=DSUM(C3:G15,5,C31:C32) field as column
=DSUM(C3:G15,"$period",C31:C32) field as field name
 

C

31

 Student

32

 Chris
Results 280.80
DAVERAGE: Averages the values in the field column of records in the database that match the criteria.
Syntax =DAVERAGE(database, field, criteria)
Example To calculate the average number of hours that the students worked during pay period 14, type the following function:
=DAVERAGE(C3:G15,F3,E34:E35) field as cell reference
=DAVERAGE(C3:G15,4,E34:E35) field as column
=DAVERAGE(C3:G15,"Hours",E34:E35) field as field name
 

E
34 PP#
35

14
Results 13 hours
DCOUNT: Counts the cells containing numbers that match the criteria in the field column of records in the database.
Syntax =DCOUNT(database, field, criteria)
Example To count the number of pay periods in which the hours are greater than 12 and less than 10, type the following function:
=DCOUNT(C3:G15,C3,H44:H45) field as cell reference
=DCOUNT(C3:G15,1,H44:H46) field as column
=DCOUNT(C3:G15,"PP#",H44:H46) field as field name
 

H
44 Hours
45 >12
46 <10
Results 8 pay periods
DCOUNTA: Counts the cells containing non-numerical data that match the criteria in the field column of records in the database.
Syntax =DCOUNTA(database, field, criteria)
Example To count the number of students that were paid $5.40, type the following function:
=DCOUNTA(C3:G15,D3,C40:D41) field as cell reference
=DCOUNTA(C3:G15,2,C40:D41) field as column
=DCOUNTA(C3:G15,"Student",C40:D41) field as field name
 

C
40 Pay rate
41

5.40
Results 1 student
DGET: Locates a single record matching the specified criteria and displays the results of the requested field. If multiple records meet the criteria, an error message will occur.
Syntax =DGET(database, field, criteria)
Example To display the student who worked 15 hours during a pay period, type the following function:
=DGET(C3:G15,D3,G37:G38) field as cell reference>
=DGET(C3:G15,2,G37:G38) field as column
=DGET(C3:G15,"Student",G37:G38) field as field name
 

G
37 Hours
38

15
Results Chris
DMAX: Returns the highest number for the field column of records matching the criteria.
Syntax =DMAX(database, field, criteria)
Example To determine the maximum number of hours worked during pay period 16, type the following function:
=DMAX(C3:G15,F3,E37:E38) field as cell reference
=DMAX(C3:G15,4,E37:E38) field as column
=DMAX(C3:G15,"Hours",E37:E38) field as field name
 

E
37 PP#
38

16
Results 11 hours
DMIN: Returns the smallest number for the field column of records matching the criteria.
Syntax =DMIN(database, field, criteria)
Example To determine the minimum number of hours worked during pay period 16, type the following function:
=DMIN(C3:G15,F3,E37:E38) field as cell reference
=DMIN(C3:G15,4,E37:E38) field as column
=DMIN(C3:G15,"Hours",E37:E38) field as field name
 

E
37 PP#
38

16
Results 8 hours
Excellence. Our Measure. Our Motto. Our Goal.