Microsoft Office Tutorials and References

In Depth Information

**Ordering Up Your Results**

Figure 3–9.
The blank cell interrupts the range to be added

What’s wrong with this picture? You want to add C18 through C24—and what you get is C22 through

C24.

But you may now understand why. AutoSum designates ranges consisting only of
consecutive

number-bearing cells; and in the case before us there’s an empty cell—C21—which breaks the

continuity. And so AutoSum frames a range that extends only as far as the consecutive string of

numberbearing cells closest to it. That’s why we see C22:C24.

But we want to add cells C18 through C24. When I’ve presented students with this problem, many

have replied that a zero could be entered in the vacant cell—a worthy suggestion, because a zero

naturally won’t alter the sum we want to compute, and because it contributes a longer, gap-free range to

the formula—C18 through C24. But I strongly advise against this tack—even though the answer it

proposes is correct. Because if you go ahead and also compute the
average
of the numbers in C18

through C24, the zero will heavily skew the result—because zero is a number, and a blank cell isn’t (as

we’ll soon see).

The by-the-book way to solve the puzzle, then, is to click in cell C25, click AutoSum, and
then drag

cells C18 through C24—in effect, overriding AutoSum’s original (C22:C24) recommendation, as seen in

Figure 3–10: