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 -
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:
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?
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:
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: