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 financial 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 first 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 filtered 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 filtered 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
filter 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 filter 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