Advertisement
Online Test Banks
Score higher
See Online Test Banks
eLearning
Learning anything is easy
Browse Online Courses
Mobile Apps
Learning on the go
Explore Mobile Apps
Dummies Store
Shop for books and more
Start Shopping

An Overview of Excel 2007's Database Functions

You can use Excel 2007's database functions to calculate statistics, such as the total, average, maximum, minimum, and count in a particular field of the database or table when the criteria that you specify are met. For example, you could use the DSUM function in an Employee database to compute the sum of all the salaries for employees who were hired after January 1, 2000, or you could use the DCOUNT function to compute the number of records in the database for the Human Resources department.

The database functions all take the same three arguments, as illustrated by the DAVERAGE function:

=DAVERAGE(database,field,criteria)

The arguments for the database functions require the following information:

  • Database specifies the range containing the database. It must include the field names in the top row.

  • Field is the argument that specifies the field whose values are to be calculated by the database function (averaged in the case of the DAVERAGE function). You can specify this argument by enclosing the name of the field in double quotes (as in “Salary” or “Date Hired”), or you can do this by entering the number of the column in the database (counting from left to right with the first field counted as 1).

  • Criteria is the argument that specifies the address of the range that contains the criteria that you’re using to determine which values are calculated. This range must include at least one field name that indicates the field whose values are to be evaluated and one cell with the values or expression to be used in the evaluation.

The following table lists the database functions available in Excel along with an explanation of what each one calculates.

The Database Functions in Excel 2007
Database Function What It Calculates
DAVERAGE Averages all the values in a field of the database that match the criteria you specify.
DCOUNT Counts the number of cells with numeric entries in a field of the database that match the criteria you specify.
DCOUNTA Counts the number of nonblank cells in a field of the database that match the criteria you specify.
DGET Extracts a single value from a record in the database that matches the criteria you specify. If no record matches, the function returns the #VALUE! error value. If multiple records match, the function returns the #NUM! error value.
DMAX Returns the highest value in a field of the database that matches the criteria you specify.
DMIN Returns the lowest value in a field of the database that matches the criteria you specify.
DPRODUCT Multiplies all the values in a field of the database that match the criteria you specify.
DSTDEV Estimates the standard deviation based on the sample of values in a field of the database that match the criteria you specify.
DSTDEVP Calculates the standard deviation based on the population of values in a field of the database that match the criteria you specify.
DSUM Sums all the values in a field of the database that match the criteria you specify.
DVAR Estimates the variance based on the sample of values in a field of the database that match the criteria you specify.
DVARP Calculates the variance based on the population of values in a field of the database that match the criteria you specify.

The example shown in the figure below illustrates the use of the database function DSUM. Cell B2 in the worksheet shown in this figure contains the following formula:

Using DSUM to total the salaries over $55,000 in an Employee database.
Using DSUM to total the salaries over $55,000 in an Employee database.
=DSUM(A3:J35,"Salary",F1:F2)

This DSUM function computes the total of all the salaries in the database that are above $55,000. This total is $468,500, as shown in cell B2, which contains the formula.

The database argument is the range A3:J35, which contains the entire database, including the top row of field names. The field argument is “Salary” because this is the name of the field that contains the values to total. Finally, the range F1:F2 is the criteria argument because these two cells contain the criteria range that designate that only the values exceeding 55000 in the Salary field are to be summed.

  • Add a Comment
  • Print
  • Share
blog comments powered by Disqus
Advertisement
Advertisement

Inside Dummies.com

Dummies.com Sweepstakes

Win an iPad Mini. Enter to win now!