Microsoft Excel 2008: Analyzing Your Database with Functions (Mac)

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.

• 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. The format is the following:

=function(database,field,criteria)

DatabaseRefers 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.
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. On the Standard toolbar, select Toolbox
   The Toolbox will appear, and it will be open to the Formatting Palette.
3. From the Toolbox toolbar, click FORMULA BUILDER
   The Formula Builder appears.
   
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.

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

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

 

 

 

 

 

 

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