Microsoft Office Tutorials and References

In Depth Information

Exhibit 4.25
Sort by $ amount for question 1

will introduce a very valuable and somewhat neglected ﬁnancial cell function called

Subtotal (function_num, ref1,

).

Question 1 is relatively simple to answer and requires the use of the
Sort
function

with
$ Amount
as the key. Exhibit 4.25 shows the results of an ascending value sort.

Record 2 is the ﬁrst record listed and the smallest
$ Amount
at $54.40 and record

3 is the last record listed and the largest at $2,543.21. Question 2 is more complex

because it requires the summation of
monthly $ Amount
totals for all account types.

There are two obvious approaches that we can consider. We can sort by
Account
,

then we can select a cell to calculate the
$ Amount
totals. This will work nicely, but

there is another approach worth considering, and one that introduces a cell function

that is extremely useful—
Subtotal (function_num, ref1,

...

)
.
Subtotal
allows you

to perform 11 functions on a subtotal—average, min, max, sum, standard deviation,

and others. As an added advantage,
Subtotal
functions with the
Filter Tool
to ignore

(not count) hidden rows that result from a ﬁltered database. Why is this important?

Recall that
Filter
and
Advanced Filter
simply hides rows that are not relevant to our

query. Thus, if we use the
Sum
function to aggregate the
$ Amount
for ﬁltered rows,

the sum will return the sum of
all
rows, visible
and
hidden.
Subtotal
will only return

the values of visible rows.

Exhibit 4.26 shows the initial step to answering question 2. The
Filter
is used to

ﬁlter for the dates of interest, 2/1/04 and 2/29/04. In Exhibit 4.27 we demonstrate

the use of
Subtotal
for all accounts during the month of February. It calculates the

sum, max, min, and count for the ﬁlter dates. Exhibit 4.27 also shows the arguments

codes (designated by the numbers 1–11) of the
Subtotal
function,
function_num
,

...

Search JabSto ::

Custom Search