Microsoft Office Tutorials and References
In Depth Information
MAX and MIN—Recording Highs and Lows
Figure 3–22. Grade averages by student
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.
The following table enumerates the relationship between the kinds of data I could enter in a cell and
what I would see in that cell. In every case, what I would copy is posted in the left column of Table 3–2
Table 3–2. Cell Entries and Cell Displays
Data Example (what you’ve typed in the cell,
which is what gets copied)
What you see in the cell
The result of whatever number you enter in T3 divided
The average of all the numbers you’ve entered in A4