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 2007

Analyzing Your Table with Functions

Database functions can make your Table 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.

NOTE: Excel Tables are simple databases, which are stored in Excel workbook files. Excel prefers the term table for its database-like tools and features in order to distinguish them from database applications such as Microsoft Access. However, the preference for this term has not been extended to the Functions dialog box yet. So, you will see the term database used extensively in this document.

return to topDefining the Syntax of Database Functions

All database functions use the same format for calculations:

=function(database,field,criteria)

Database
Refers to the range of cells that make up the Table. 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. From the Formulas tab, 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.
    Function Arguments dialog box

  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 Database Functions, 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 specified field of the Table that match the criteria.
Syntax =DSUM(database, field, criteria)
Criteria
Example To calculate the total amount that Chris was paid, type the following function:
=DSUM(C3:G15,G3,C31:C32)
field designated as a cell reference
OR
=DSUM(C3:G15,5,C31:C32)
field designated as a column
OR
=DSUM(C3:G15,"$period",C31:C32)
field designated as a field name
 

C

31

 Student

32

 Chris
Results 280.80
DAVERAGE: Averages the values in the specified field of the Table that match the criteria.
Syntax =DAVERAGE(database, field, criteria)
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 designated as a cell reference
OR
=DAVERAGE(C3:G15,4,E34:E35)
field designated as a column
OR
=DAVERAGE(C3:G15,"Hours",E34:E35)
field designated as a field name
 

E
34 PP#
35

14
Results 13 hours
DCOUNT: Counts the cells containing numbers that match the criteria in the specified field of the Table.
Syntax =DCOUNT(database, field, criteria)
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:H46)
field designated as as cell reference
OR
=DCOUNT(C3:G15,1,H44:H46)
field designated as a column
OR
=DCOUNT(C3:G15,"PP#",H44:H46)
field designated as a 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 specified field of the Table.
Syntax =DCOUNTA(database, field, criteria)
Criteria
Example To count the number of students that were paid $5.40 during pay period #16, type the following function:
=DCOUNTA(C3:G15,D3,C40:D41)
field designated as a cell reference
OR
=DCOUNTA(C3:G15,2,C40:D41)
field designated as a column
OR
=DCOUNTA(C3:G15,"Student",C40:D41)
field designated as a field name
 

C

 D
40 Pay rate PP#
41

5.40

 16
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)
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 designated as a cell reference
OR
=DGET(C3:G15,2,G37:G38)
field designated as a column
OR
=DGET(C3:G15,"Student",G37:G38)
field designated as a field name
 

G
37 Hours
38

15
Results Chris
DMAX: Returns the highest number for the specified field in the Table which matches the criteria.
Syntax =DMAX(database, field, criteria)
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 designated as a cell reference
OR
=DMAX(C3:G15,4,E37:E38)
field designated as a column
OR
=DMAX(C3:G15,"Hours",E37:E38)
field designated as a field name
 

E
37 PP#
38

16
Results 11 hours
DMIN: Returns the smallest number for the specified field in the Table which matches the criteria.
Syntax =DMIN(database, field, criteria)
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 designated as a cell reference
OR
=DMIN(C3:G15,4,E37:E38)
field designated as as column
OR
=DMIN(C3:G15,"Hours",E37:E38)
field designated as as field name
 

E
37 PP#
38

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