Microsoft Office Tutorials and References
In Depth Information
Working with Database Functions
Working with Database Functions
The database functions all work in basically the same way. They perform
some calculation on a specified field for those records that meet specified
criteria. For example, you can use a database function to calculate the
average final grade for all students in Accounting 101.
All database functions use the following three arguments:
The database range: This argument tells the function where the
database is. You enter it using cell addresses (for example, A1:D200) or
a named range (for example, Students). The range must include all
records, including the top row of field names.
The field: You must tell a database function which field to operate
on. You can’t expect it to figure this out by itself! You can enter either
the column number or the field name. A column number, if used, is
the number of the column offset from the first column of the database
area. In other words, if a database starts in Column C, and the field is
in Column E, the column number is 3, not 5. If a heading is used, put it
inside a set of double quotation marks. Database functions calculate a
result based on the values in this field. Just how many values are used
depends on the third argument — the criteria.
The criteria: This tells the function where the criteria are located — it
is not the criteria per se. The criteria tell the function which records to
use in its calculation. You set up the criteria in a separate part of the
worksheet, apart from the database area. This area’s address is passed
to the database function. Criteria are explained in detail throughout the
chapter.
Establishing your database
All database functions take a database reference as the first argument. The
database area must include headers (field names) in the first row. In Figure 17-1,
the first row uses Student ID, Class, Teacher, and Final Grade as headers to
the information in each respective column.
A great way to work with the database functions is to name the database area
and then enter the name, instead of the range address, into the function.
Search JabSto ::




Custom Search