Microsoft Office Tutorials and References
In Depth Information
Copying Formulas: More Than Just Duplication
The advantage of using the cell reference is that if the teacher decides to change the
grade bonus, all she needs to do is type that new bonus in cell N7 and John’s grade will
change automatically. The next step, then, is to copy this new formula to all the other
students down the L column, as before, using any of the methods described previously.
But this time something is going to go wrong.
When you go ahead with our formula copy, you’ll see the content of Figure 4–25.
Figure 4–25. Not much of a bonus! Note the 4 points in cell N7.
What went wrong is that it appears that only John received the bonus. Why hasn’t
anyone else received it?
Absolute Addressing: When We Don’t Want It to Work
Here’s the answer. If you look at cell L9—the cell containing Bill’s “bonus” grade (all you
have to do is click L9 and look at the formula bar)—you’ll see the following:
Well, that’s supposed to happen, because of the relative cell addressing feature
introduced previously. But what value is in cell N9? Nothing; cell N9 is blank. And 91
plus nothing is 91. Bill’s score is unchanged.
And if you look at Dorothy’s cell, L10, you’ll see
Same problem. Both cell references have changed as we would expect, but again,
there’s nothing in cell N10—and so on for all the other students. Only John’s bonus has
kicked in. It’s all because here we want all the students’ formulas to refer to cell N8, the
cell in which the bonus is stored. But because copied formulas exhibit relative cell
addressing, they change their references relative to their distance from the source cell.
What to do?