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.