Microsoft Office Tutorials and References
In Depth Information
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
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.
Search JabSto ::




Custom Search