Not Just Your Average Function in Excel 2010 Part 2

But let’s return to the entries in the AutoSum menu. MAX and MIN are almost self-evident; they identify the highest and lowest  cells in a range while ignoring blank cells (and text entries), an important omission in these regards. After all, treating blanks as  zero could erroneously yield a MIN of zero among a range of other cells—and if you’re working with a range consisting entirely of  negative numbers, counting a blank cell as zero could yield a MAX of…zero! And you’ll doubtless be able to find a broad niche  for MAX and MIN in your spreadsheet doings.

And by way of recapitulation—all the functions assigned a place on the AutoSum drop-down menu are written the same  way:=NAME (range, or ranges) I’m harping on this point because a great many of the other functions in the Excel repertoire  display a different syntax, as you’ll see.

And while we’re talking about SUM, COUNT, AVERAGE and the like, here’s another handy but easyto- miss, hidden- in-plain-sight take on these operations and some more. If you select any set of cells containing numbers (at least two cells, to be  exact) and train your gaze on the lower right of the worksheet screen, on what’s called the Status Bar, you’ll suddenly see a see a  mini-report about the data you’ve highlighted (Figure 3–20):

There it is—the Average, Count, and Sum of the range you’ve selected. Just know that the Count here is really COUNTA; that is, it will count any data in the cell—values, text, and even formulas. What we’re presented with here is an on-the-fly summary  of the data in the selected range. None of this information will actually appear in the worksheet—and once you move  elsewhere to other cells, those figures will change. Still, if you need a quick improvised read on certain data, select that range  and take a look.

And what you see there can be customized, at least within limits. If you right-click anywhere on the status bar, you’ll trigger a  towering short-cut menu, which, among other things, allows you to add three more calculations to the bar (Figure 3–21):

Thus Numerical Count (that is, COUNT), Max, and Min can take their place on the Status Bar too if you need them; simply  click them on.

But now that we’ve gotten our feet wet in the Olympic-sized pool of functions, grab a towel and sit back, because we’ve some  more copying and moving to do, of a different and most important kind.

And in this connection, let’s go all the way back to that sample grade-average worksheet I served up in Chapter 1, the one  festooned with red highest-score cells and those Sparklines. Don’t remember it? I’m not taking it personally—look at Figure   3–22 for a refresher:

 

Now look at the Formula Bar. I’ve clicked cell I10, the one bearing Alice’s exam average, and its means of  calculation—using AVERAGE, of course—–is recorded up there in that bar. By way of review, we see that Alice’s grades  occupy range D10:H10 and, by inserting that range reference between  AVERAGE’s parentheses, we determined her average  grade was 83.0. The point is this: What if I have 150 students in the class (and I’ve had more than that on occasion), and I need  to figure the test average for each and every one of them? Do I have to click the AutoSum button 150 times in order to  carry out that disagreeable task? Yes, that sounds like a rhetorical question, and it is. The answer to it is no, because what we  can do instead is copy Alice’s AVERAGE formula down the I column for as many rows of students as I need.

Yes, this is a have-to-know, because copying a formula—which entails in essence copying cell references— is something  new—and vital—to the your understanding of how Excel works.

But in fact the ways of actually copying cell references are identical to the ones we described earlier (and we’re going to learn  an additional one soon); what’s different is what happens when you copy them. And that preamble raises a larger point. All the  cell copying we’ve discussed to date and will continue to discuss in this chapter entail copying whatever we enter in a cell—as  opposed to what we see in the cell. If I copy Alice’s AVERAGE elsewhere, I am most assuredly not copying her average of 83. Rather, I’m copying what I typed in cell I10—the formula that calculated her 83.