Not Just Your Average Function in Excel 2010 Part 1

How do these work and what do they do? To answer those questions in order—these functions behave virtually identically to  SUM, in the sense that they’re written in the same manner, with the same kinds of range references and issues as described above.

Moreover, each of these has an “Auto” character—meaning that if you click beneath a column or to the right of a row of numbers, you can click on their names and install them in the desired cell—just as with AutoSum.

Now let’s explain what these functions actually do.

VERAGE is a spreadsheet staple that performs as advertised—it computes the average of a range, or a set of selected cells.  Again, its structure is a virtual clone of SUM, so for example: =AVERAGE(D23:D42).

returns the average of that range. As implied earlier in our discussion of SUM, AVERAGE ignores any blank cell in a range,  refusing to treat it as the mathematical equivalent of zero. Thus AVERAGE yields a result of 8 for the range below, and not the  6.4 you’d compile if the blank cell were assigned an ad hoc value of zero. 5 , 7, 8 , (blank cell) , 12  And once again, if your data are  arrayed conventionally in one column or row, you can click on Average in the AutoSum drop down menu, press Enter, and   AVERAGE will be posted in the cell you’ve selected (Figure 3–16):

Figure 3–16. Selecting AVERAGE You can incorporate multiple ranges in AVERAGE as well. (Your decimal point questions  will be taken up in the next chapter).

Making Range Values COUNT And what about the third entry in the AutoSum drop-down menu, Count Numbers? Here  Excel’s menu description doesn’t quite match the actual name of the function it generates —COUNT—and for a reason, which  we’ll explain right after we let you know what COUNT actually does. (Figure 3–17):

COUNT simply counts all the cells in a range (or ranges) which contain numbers—nothing more mathematical then that. Thus in the above screen shot, if I click cell C17 and select COUNT—and the method for doing this is identical to AVERAGE and  SUM—I’ll drum up a result of 4. But substitute a text entry for any the cells in Figure 3–16 and my formula result now reports  3. You may want to ask rather compelling question about COUNT: namely, why would I need to use it?

By way of illustration—if you’re a teacher, you may need to tally the number of tests the students in your class have taken, for  example. Consider the scenario below, in Figure 3187:

COUNT will thus tell you that Bill has taken 3 exams and Jack has attempted 2, and so needs to make one up. Or, by way of  additional example: if you’ve entered a list of potential donors to a charity on your sheet and key in the amount of each  contribution when it’s received, you’d use SUM to learn how much money you’ve taken in to date, and COUNT to let you  know how many individuals have donated, by counting each donation. And while you’re at it, you could compute the average  size of the donations, couldn’t you?

But why does Excel advertise COUNT as Count Numbers on its drop-down menu? It does so in order to distinguish COUNT  from another function—COUNTA—which counts all the cells in a range that contain any kind of data. Thus for the range  shown in (Figure 3–19):

Figure 3–19. COUNT or COUNTA—Different results this for range COUNT will return 3, COUNTA 4. (COUNTA, by the way,  is written in the same way as COUNT, though it’s not included in the above drop-down menu.)