Microsoft Office Tutorials and References

In Depth Information

**More of the Same**

When you see the one you want, you can either double-click its name, or scroll down to the function

in question with the Down arrow key and press Tab (but not Enter). You’ll see, for example (Figure 3 -

33):

Figure 3–33.
Function writing assistance on tap—the tap of the Tab key

Then start to type the remainder of the functions. True, you’ll have to
know
what to type next, but

that’s going to come with repetition. (Note the small caption that offers a kind of running commentary

about which argument you’re currently entering between the parentheses, e.g., which number range

you’re now identifying in COUNT. But don’t worry—more on arguments soon).

The first thing you want to know about functions and formulas (remember that functions are

builtin Excel formulas) is that they can be mixed and matched in innumerable ways. They needn’t be

composed and applied in isolation, and can be related to each other in the same formula, and for a

myriad of purposes. So start priming that spreadsheet imagination.

For example, consider this formula:

=AVERAGE(B3:E3)+5

This could, for example, be used to calculate a student’s average for four exams (spanning columns

B through E), to which 5 points are added—as a kind of bonus.

Now how about this?

=MIN(B3:E3)*1.05

We’re working with same four tests. Here our beneficent instructor is adding 5 percent to a student’s

lowest—that is, minimum—score. Not five
points
mind you, but 5
percent
. Thus if our student bombed
,

test number 3 with a 58, the above formula will take that score and multiply it by 1.05, coming up with

60.9. Of course, if our teacher is as beneficent as we say, she’ll round it up to 61.

Note, by the way, that both of these formulas factor in
both
a function and an actual, garden-variety

number. That’s part of Excel’s mix-and-match capability.

Now think about this one:

=(SUM(B3:E3)-MIN(B3:E3))/(COUNT(B3:E3)-1)

True, this one looks scary—at least at first, and perhaps even second perusal. But in reality, it

doesn’t introduce any feature that we haven’t already learned. What this formula does is add the scores

of all four exams, and subtracts from that total the
lowest
score. It then divides this new result by the

number of remaining exams, that is, 3. In effect the formula calculates the average of the three highest

exam scores, having dropped the lowest score.

Let’s look at this one more closely—and I’ll submit the hope that, upon reflection, you’ll agree the

formula isn’t quite as daunting as you may suppose.

Let’s assume our student has scored 76, 82, 58, and 91 on the quartet of exams. Note the entire

formula as usual begins with an = sign. But then note that a pair of parentheses surrounds
both
the SUM

and MIN parts of the formula together, this in addition to the parentheses surrounding the individual

ranges identified in SUM and MIN. Thus observe the two consecutive parentheses following the B3:E3

range reference in MIN. One simply serves as the closing parenthesis in MIN’s own range; the other

bounds off the combined SUM-MIN expression, thus letting us compute this total: