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 2008

Analyzing Your Database with Functions

Database functions can make your worksheet more useful by extracting information about the data. You can extract this information 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.

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.

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

  2. On the Standard toolbar, select ToolboxToolbox button
    The Toolbox will appear, and it will be open to the Formatting Palette.

  3. From the Toolbox toolbar, click FORMULA BUILDER Formula Builder icon
    The Formula Builder appears.
    Formula Builder, database average function displayed

  4. In the scroll list, double click the appropriate function
    An Arguments section appears.

  5. Complete the function arguments
    As you click each argument, a description of what is required appears in the Description section.

  6. Press [Return]
    The results of the function will appear.

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.