Microsoft Office Tutorials and References
In Depth Information
Working with Database Functions
5. Click the OK button to save the reference change and close the
6. Click Close.
If you add records to your database range by inserting new rows somewhere
in the middle, rather than adding them on at the end, Excel automatically
adjusts the reference to the named range.
Establishing the criteria area
As I have mention earlier, the criteria are not part of the database function
arguments but are somewhere in the worksheet and then referenced by the
function. The criteria area can contain a single criterion, or it can contain two
or more criteria. Each individual criterion is structured as follows:
✓ In one cell, enter the field name (header) of the database column that
the criterion will apply to.
✓ In the cell below, enter the value that the field data must meet.
Figure 17-3 shows the student database with a criteria area to the right of the
database. There are places to put criteria for the Class, Teacher, and Final
Grade. In the example, a criterion has been set for the Class field. This
criterion forces the database function to process only records (rows) where the
Class is Accounting 101. Note, though, that a criterion can be set for more
than one field. In this example, the Teacher and Final Grade criteria have
been left blank so they don’t affect the results.
The DAVERAGE function has been entered into cell F8 and uses this
criteria range. The three arguments are in place: The name Students tells the
function where the database is, the Final Grade field (column) is where the
function finds values to calculate the average, and the criteria are set to the
worksheet range that has criteria that tell the function to use only records
where the Class is Accounting 101 — in other words, F2:H3. The entry in cell
F8 looks like this:
Why does this function refer to F2:H3 as the criteria range when the only
defined criterion is located in the range F2:F3? It’s a matter of convenience.
Because cells G3 and H3 in the criteria range are blank, the Teacher and Final
Grade fields are ignored by a database function that uses this criteria range.
However, if you want to enter a criterion for one of those fields, just enter it
in the appropriate cell; there is no need to edit the database function arguments.