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 ,