Ordering Up Your Results in Excel 2010

Digression number 2. There’s another tricky set of rules you will need to understand—or review, as the case may be—because  you probably had to put up with some of these in school: the order of operation.

For example, what’s the answer to this formula? =4*5+7 .

It could be 27—that is, 4×5 plus 7—or 48—4×12. Which is it? In this particular case you can resolve the problem by  surrounding the relevant numbers with parentheses, e.g., =(4*5)+7 Or  =4*(5+7).

You get the idea. Excel resolves this kind of ambiguity with a set of orders of operation—a kind of priority listing which  declares which operation takes precedence—that is, is calculated first—in a formula. (In the cases above, the values flanked by parentheses are treated as a unit.) The order reads like this:

Parentheses
Exponents
Multiplication
Division
Subtraction
Addition

Let’s illustrate this hierarchy with a few cases. This formula: =4+5/2 .

results in an answer of 6.5. It divides 5 by 2 and then adds 4—because priority goes to division over addition.

This formula: =4*5/2.

results in 10, because the multiplication—4 times 5—is carried out first. That result—20—is then divided by 2.

This formula, however: =4*(5/2).

also yields 10, but this time because the parenthetical expression—(5/2) that is, 2.5—has priority over any other operation.

Now let’s get back to our regularly scheduled program—this matter of adding numbers via a formula. While the method we  recounted above surely works, it conceals a problem. What if you need to add 20,000, or even 200, numbers? You won’t want to click on each and every one of those cells as per our initial method—and, given Excel’s cell character limit, you may not be  able to do it anyway. So what’s the alternative? Good question. The answer takes us to the first and most important of Excel’s  built-in operations, or functions, called SUM. How does SUM work? For introductory purposes, we’ll demonstrate the  standard way to implement SUM in a worksheet—with the AutoSum command, which is actually stored in two different tabs  (remember that term?) in slightly different guises—Home and Formulas, shown in Figures 3–4 and 3–5:

And once you appreciate how AutoSum works—and it’s rather simple—you’ll go a long way toward firming your grasp of the  whole function genre.

So let’s try the following: click on the Home tab, if you’re not already there. Delete your answer in cell B15, stay in that cell,  and click AutoSum. You should see Figure 3–6:

Figure 3–6. A more efficient way to add all those cells Then press Enter, and presto—your answer should materialize.

You see what’s happened. AutoSum installed a function—SUM—into the cell in which you clicked, a cell that happens to be  positioned directly below the range of numbers we wanted to add; and that’s a range SUM automatically identified. That’s why  it’s called AutoSum.

But before we return to the workings of SUM in particular, note some very basic principles of Excel functions. First, apart  form the equals sign (=), an open parenthesis always follows the name of the function—here, SUM. Then some additional  information—which could be a range and/or some other entries, as you’ll see—follows, after which the expression is concluded  with a close parenthesis. To summarize the basic syntax for any function:

=NAME(various data in here) Remember that the equals sign always appears at the outset of any formula, e.g, =67+SUM(B6:B15000). (Here we’re describing the basic syntax of a function considered alone.) What kind of data gets  interposed between the parentheses depends on the function, as you’ll see; here, in our current case, SUM identifies the range  to be added.

Now back to AutoSum. We see that AutoSum indeed correctly identified the range we wanted to add—and had that range, for  example, been B6:B15000 instead, we could have clicked on cell B15001, and proceeded to click AutoSum. We’d then see this  in B15001: =SUM(B6:B15000) We’d go on to press Enter, and the numbers in all those cells would be added.

Thus we’re beginning to infer what exactly it is that AutoSum does. It’s programmed to automatically identify a range of  consecutive numbers to be added in the column or row in which you’ve clicked. And you needn’t click in the cell immediately  below the column or immediately to the right of the row containing the numbers—just somewhere in that column or row. Thus  AutoSum will work in the example shown in Figure 3–7, too:

Figure 3–7. This time it’s a row being added Starting to get the point? Here we’re adding a row of numbers, even as we’ve  spaced the AutoSum cell two columns away from the last number—and Excel doesn’t mind. And look at the range SUM  identified— it’s included the two empty cells to the left of AutoSum, as the formula itself appears in H16.

In sum (pun intended) and by default, AutoSum will begin to total all consecutive numbers in the column or row in which it’s been positioned. But what if you see something like Figure 3–8 in cells C18 through C24?

Figure 3–8. Mind the gap…what about that blank cell?

If you click on cell C25 and turn to AutoSum, the resulting formula will read (Figure 3–9):

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:

Figure 3–10. Drawing a blank: or rather, drawing your range over a blank cell separating two sets of numbers