Microsoft Office Tutorials and References
In Depth Information
Exhibit 4.27 Calculation of subtotals of $ amount for questions 2 and 3
Question 4 is relatively easy to answer with the tools and functions that we have
used— Filter and Subtotal . Exhibit 4.28 shows the outcome of the query for the
account Ofﬁce Supply and the speciﬁc subtotals of interest—average ($714.24), max
($1,669.76), sum ($10,713.67), and count (15).
Question 5 requires the use of the Advanced Filter tool. Exhibit 4.29 shows the
outcome of the Filter for Project X and Y. Note that the pull down menu (in box)
associated with Comment ﬁeld permits you to identify the Projects of interest—
Project X and Y . Unfortunately, the Filter tool is unable to perform the Date Rcvd.
ﬁltering. Thus, we are forced to employ the Advanced Filter tool. In Exhibit 4.30,
the Advanced Filter query is shown. It employs some repetitive entries, but it is rel-
atively simple to construct. Let us concentrate on the ﬁrst two rows in the Advanced
Filter criteria range (Excel rows 45 and 46), because they are similar in concept to
the two that follow. The ﬁrst two rows can be read as follows:
1. Select records that contain Date Rcvd dates after or equal to 1/1/04 and records
that contain Date Rcvd dates before or equal to 1/13/04 and records that contain
Comment entry Project X . These queries are found on rows 45 of Exhibit 4.30.
Note the importance of the and that is implied by the entries in a single row.
2. Recall that each row introduces and or condition. Thus, the second row of the
criteria can be read as— or select records that contain Date Rcvd dates after or
equal to 3/1/04 and that contain Comment entry Project X . These queries are
found on row 46 of Exhibit 4.30.
3. For Project Y , rows 47 and 48 repeat the process.