Microsoft Office Tutorials and References
In Depth Information
Absolute References: Absolutely Important
Absolute References: Absolutely Important
But if I go ahead and enter =D11+A11 in Gordon’s E11 cell, and once again copy down the E column,
I’ll see this (Figure 3 - 27):
Figure 3–27. Nice try, but wrong answers. We’ll explain why.
Hmmm. That doesn’t look quite right, does it? Gordon’s score surely exhibits the 5-point increment,
but his colleagues seem to have come away with nothing extra at all. What’s happened?
What’s happened is this: Gordon’s bonus-conferring =D11+A11 is correctly written; it references
both his test score—64, in D11—and the 5-point give-away, stashed in cell A11. But when I copy this
spot-on formula down to April’s cell in E12, her formula states:
=D12+A12
and therein lies the problem. Because relative referencing has done its thing, both row numbers in
April’s formula have pumped to 12, up one from Gordon’s 11. And even though cell E12 correctly cites
Alice’s original test score, cell A12 contains…nothing. And 49 plus nothing... is 49. And that also means
that Tony’s cell bonus formula—=D13+A13—has to be wrong, too, because A13 is likewise blank, and so
on. So apart from Gordon’s original bonus calculation all the other students report the wrong bonus
result, because they don’t reference the cell—A11—in which the bonus is entered. So how is this
puzzlement resolved?
—and edit the cell to read:
=D11+A\$11
Then copy this revised version down the E column to all the other students. You should now be
viewing the correct, bonus-bearing grades for each student. So what’s going on? Obviously the dollar
sign has something to do with it.
First, we need to understand that the dollar sign has nothing at all to do with currency formatting.
Rather, the sign is a programming convention, which freezes the part of the cell address to its immediate
right. Installing the dollar sign where we did—alongside the 11 in A11—means that no matter where we
copy Gordon’s =D11+A\$11, that 11 will never change. Thus April’s formula now states:
=D12+A\$11
and Tony’s declares:
=D13+A\$11

Search JabSto ::

Custom Search