Page 232 - Microsoft Office Excel 2003 Programming Inside Out

Microsoft Office Excel 2003 Programming Inside Out

Manipulating Numbers

The topics discussed in the following section are, strictly speaking, part of the standard func­
tioning of an Excel worksheet and not VBA programming. That being said, there are a num­
ber of financial calculations that you perform frequently in business settings, so they’re
included in this book. These functions are discussed in the context of VBA routines, but there
will be enough examples of how to use the financial functions in your worksheets so that
you’ll learn how to use them as formulas, too.

Performing Summary Calculations

One of the strengths of the Excel spreadsheet program is that you can summarize worksheet
data in many different ways, but one of its weaknesses, at least in terms of relatively new users
taking advantage of those features, is that you need to know they’re there. Table 9-2 lists the
mathematical operations (and a few other operations) that you can use to summarize the
data in a worksheet.

Table 9-2. The Most Common Summary Calculations You’ll Perform in Excel
Function Description
AVERAGE Finds the arithmetic average (mean) of a data set
Chapter 9
COUNT Counts the number of cells in a range
COUNTA Counts the number of non-blank cells in a range
COUNTBLANK Counts the number of blank cells in a range
COUNTIF (range, criteria) Counts the number of cells in a range that match a given
criteria
MAX Finds the largest value in a range
MEDIAN Finds the median value or values (the value or the value
pair closest to the average) of a range
MIN Finds the smallest value in a range
MODE Finds the most common value in a range
STDEV Finds the standard deviation of the values in a range
SUM Finds the arithmetic sum of the values in a range
SUMIF(range, criteria) Finds the arithmetic sum of the values in a range that meet
a given criteria

So now you know what the basic summary functions are, but how will your colleagues know
which operations they can use? Simple: you tell them. You can list the available operations in
a cell, a comment, or a text box that you place beside the data.
The following procedure is an example of how you might go about allowing your colleagues
to identify which data to summarize and to select which summary operation to perform.

206
Part 4: Advanced VBA