Microsoft Excel 2007: Analyzing Your Table with Functions (Win)

 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.

• Defining the Syntax of Database Functions
• Performing Database Functions
• Examples of Database Functions

Defining the Syntax of Database Functions

All database functions use the same format for calculations:

=function(database,field,criteria)

DatabaseRefers 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.
FieldIndicates 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.
CriteriaCriteria 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.

Performing 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 FUNCTION
   The Insert Function dialog box appears.
   
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 DIALOG
   3. Select the data range
   4. Click RESTORE DIALOG
   5. Repeat steps a-d until all text boxes are completed
7. To perform the function, click OK

Examples 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:

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 Make sure to let me know in the comments below or on our Facebook page how you've got with it or Do you have any question