Microsoft Office Tutorials and References
In Depth Information
Using Database Functions
Gender = F
or Sales Area
= Outside
Figure 5–64
Using Database Functions
Database Functions
Database functions are
useful when working
with lists of data, such as
the one in this chapter.
Remembering the
function arguments and
their order within
parentheses is not easy,
however. Thus, it is
recommended that you
use the Insert Function
button in the formula
bar to assign a database
function to your
worksheet.
Excel has 12 database functions that can be used to evaluate numeric data in a table.
One of the functions is called the DAVERAGE function. As the name implies, the
DAVERAGE function is used to fi nd the average of numbers in a table fi eld that pass a
test. This function serves as an alternative to fi nding an average using the Subtotal button
on the Data tab on the Ribbon. The general form of the DAVERAGE function is:
=DAVERAGE(table range, “fi eld name”, criteria range)
where table range is the range of the table, fi eld name is the name of the fi eld in the table,
and criteria range is the comparison criteria or test to pass.
Another often used table function is the DCOUNT function. The DCOUNT
function will count the number of numeric entries in a table fi eld that pass a test. The
general form of the DCOUNT function is:
=DCOUNT(table range, “fi eld name”, criteria range)
where table range is the range of the table, fi eld name is the name of the fi eld in the table,
and criteria range is the comparison criteria or test to pass.
To Use the DAVERAGE and DCOUNT Database Functions
The following steps use the DAVERAGE function to fi nd the average age of female
sales reps and the average age of male sales reps in the table. The DCOUNT function is
used to count the number of sales reps records that have a grade of A. The fi rst step sets
up the criteria areas that are required by these two functions.
1 Select cell O1 and then enter Criteria as the criteria area title. Select cell L1, click the
Format Painter button on the Ribbon, and then click cell O1. Center the title, Criteria,
across the range O1:Q1.
2 Select cell O2 and then enter Gender as the fi eld name. Select cell P2 and enter Gender as
the fi eld name. Select cell Q2 and then enter Grade as the fi eld name. Select cell L2. Click
the Format Painter button on the Ribbon. Drag through the range O2:Q2.
3 Enter F in cell O3 as the Gender code for female sales reps. Enter M in cell P3 as the Gender
code for male sales reps. Enter A in cell Q3 as the Grade value. Select M3, click the Format
Painter button on the Ribbon, and then drag through the range O3:Q3.
4 Enter Average Female Age = = = = = > in cell O4. Enter Average Male Age = = =
= = = => in cell O5. Enter Grade A Count = = = = = = = = = > in cell O6.
5 Select cell R4 and then enter =daverage(a8:j21, “Age”, o2:o3) as the database
function.
6 Select cell R5 and then enter =daverage(a8:j21, “Age”, p2:p3) as the database
function.
 
 
Search JabSto ::




Custom Search