Module Five: Excel Database Functions
The Excel Database Functions are designed to assist you when working with a large table of data, where each row in the table stores an individual record.
The syntax of all database functions are the same:
Function_name (database, field, criteria)
- Database – range of cells, where your database is
- Field – name or numer of column where values are
- Criteria – your criteria – they should contain name of column and name of some value from that column
Each column in the spreadsheet table stores a different field (or type of information) for each record. The database functions perform basic operations, such as count, max, min, average, sum,product and statistical calculations. Unlike other Excel functions, they enable the user to specify criteria, so that the operation is performed only on on matching records (rows). Other records in the database are ignored.
| Function | Description |
|---|---|
| DAVERAGE | Returns the average of selected database entries |
| DCOUNT | Counts the cells that contain numbers in a database |
| DCOUNTA | Counts nonblank cells in a database |
| DGET | Extracts from a database a single record that matches the specified criteria |
| DMAX | Returns the maximum value from selected database entries |
| DMIN | Returns the minimum value from selected database entries |
| DPRODUCT | Multiplies the values in a particular field of records that match the criteria in a database |
| DSTDEV | Estimates the standard deviation based on a sample of selected database entries |
| DSTDEVP | Calculates the standard deviation based on the entire population of selected database entries |
| DSUM | Adds the numbers in the field column of records in the database that match the criteria |
| DVAR | Estimates variance based on a sample from selected database entries |
| DVARP | Calculates variance based on the entire population of selected database entries |