The 12 database functions let you find and perform calculations on specific pieces of data in a database. All of the database functions search a specified database for records that match specified criteria. Some of the database functions then perform calculations on data in a specified field of the matching records.
The following are the database functions.
| DAVERAGE | DGET | DPRODUCT | DSUM |
| DCOUNT | DMAX | DSTDEV | DVAR |
| DCOUNTA | DMIN | DSTDEVP | DVARP |
The following worksheet showing salary, commission, and sales information for a company's salespeople can be used as a database.
You can use the DAVERAGE database function to find the average commissions earned in 1996 by all salespeople who made less than $29,000 in salary that year. The arguments for DAVERAGE -- database, field, and criteria -- are shown in the illustration above. The function searches database for records that comply with criteria, then finds the average of field in the records that pass the test.
This function returns 24,497, the average commissions of the three salespeople (Brad, Denise, and James) whose salaries are less than $29,000:
All database functions have the same three arguments: database, field, and criteria.
Database is a reference to a range of cells containing data that the database function searches. Each row of data in the database is called a record.
The top row must contain unique text labels identifying the data in the columns. To use numbers or dates as labels, identify them as text by entering a leading apostrophe ('). Since the labels must match the labels in field and criteria, it's best to make them simple and descriptive, with no extra spaces. In general, names should be free of punctuation marks and other symbols.
Field is the column within database containing the data on which you want the function to perform the calculation. You may enter the column's label enclosed in quotation marks or a number corresponding to the column's position within the database: the first column is 1, the second column is 2, etc. You may also enter a reference to a cell that contains the column's label or number.
Criteria is a reference to a range of cells, usually separate from database, containing search criteria. The top row contains text labels that match the labels in database. The cells beneath the labels contain criteria that data in the appropriate column of database must pass in order for that record to be selected for the function's calculation.
The criteria can be numbers, text, or blank cells. You can enter the criteria directly in the cell, or you can enter formulas and functions that calculate the criteria. You may also enter cell references.
Note Microsoft Excel does not support formulas or functions in database criteria. If your worksheets must be compatible with Microsoft Excel, do not use formulas or functions in your criteria.
Numbers will be compared to the numbers in database. Only exact matches pass the test. An = before the number is optional.
To test for relationships between the criteria and the numbers in database, precede the criteria with the operators <, >, <=, >=, and <> (not equal). For example, <500 will find all numbers less than 500.
Text will be compared to text in database. Only exact matches pass the test. The function does not consider the case of the letters, so the criteria DENISE will find Denise and denise. Do not use quotation marks (unless you want the database function to find matches containing the quotation marks).
To test for words close to a particular word, use the wildcard characters ? and *. The ? stands in for exactly one character; * stands for zero or more characters. You may use more than one wildcard character in a cell. Any characters after the * wildcard must be located at the end of the cell text. For example, the criteria JA*S will find JAMES and JARS.
To test for text that is alphabetized before or after the criteria, use the operators <, >, <=, and >=. For example, the criteria >BRAD will find all cells that start with text that is alphabetized after the word BRAD. To test for text that is not the same as the criteria, use the operator <> (not equal). For example, the criteria <>FRED will find all cells that do not contain just the word FRED.
A blank cell indicates that no tests should be performed. A blank row in the criteria range indicates all records should be selected.
Functions in criteria let you create sophisticated search strategies that are easy to manipulate. For example, to find all dates that are earlier than today, you can enter the following function as criteria:
Avoid using the following types of functions in criteria ranges:
When creating criteria, you can use cell references. For example, to find all dates that match the date in cell H14, enter the following criteria:
You can also create complex criteria that refer to cells in the database. For example, say you have a database that contains customer names in column A and merchandise order dates in column B:
To search for all records with order dates within the last month, and to ensure that the formula you enter will always be updated to the current date, you can enter the following criteria:
The function will perform the test on cell B2, the topmost data cell in the order dates field. It will then perform the test on B3, B4, and so on.
You can use multiple nested functions and refer to one or more database column to create complex selection criteria.
When your criteria contains cell references, follow these guidelines:
A criteria range that contains more than one row or column has multiple criteria.
When more than one cell in the first criteria row contains criteria, the function finds only the records that pass all the tests. You may use labels more than once in a criteria range to establish multiple criteria on a single field. For example, the following criteria range would find database records in which the 1996 salaries are between $29,000 and $33,000:
You may also enter more than one row of criteria. If a record passes a test for one criteria row, it is picked regardless of whether it passes the tests in the other criteria rows. For example, the following criteria range would find all database records in which the 1996 salaries are either below $29,000 or above $33,000:
