Microsoft Office Tutorials and References
In Depth Information
Using Database Functions
Keep these following points in mind when using computed criteria:
h Computed criteria formulas are always logical formulas: They must return either TRUE or
FALSE. However, the value that’s returned is irrelevant.
h When referring to columns, use a reference to the cell in the first data row in the field of
interest (not a reference to the cell that contains the field name).
h When you use computed criteria, do not use an existing field label in your criteria range.
A computed criterion essentially computes a new field for the table. Therefore, you must
supply a new field name in the first row of the criteria range. Or, if you prefer, you can
simply leave the field name cell blank.
h You can use any number of computed criteria and mix and match them with
noncomputed criteria.
h If your computed formula refers to a value outside the table, use an absolute reference
rather than a relative reference. For example, use $C$1 rather than C1.
h In many cases, you may find it easier to add a new calculated column to your worksheet
database or table and avoid using computed criteria.
Using Database Functions
To create formulas that return results based on a criteria range, use Excel’s database worksheet
functions. These functions all begin with the letter D, and they are listed in the Database category
of the Insert Function dialog box.
Table 9-4 lists Excel’s database functions. Each of these functions operates on a single field in the
database.
Table 9-4: Excel’s Database Worksheet Functions
Function
Description
DAVERAGE
Returns the average of database entries that match the criteria
DCOUNT
Counts the cells containing numbers from the specified database and criteria
DCOUNTA
Counts nonblank cells from the specified database and criteria
DGET
Extracts from a database a single field from 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 of the selected database entries (assumes that the data
is a sample from a population)
 
Search JabSto ::




Custom Search