Microsoft Office Tutorials and References
In Depth Information
Partially Fixed References
Copying Formulas
Figure 17-14:
In this version of the
student grade book,
both the tests and
the assignment are
graded on different
scales (as listed in
row 12). Thus, the
formula for
calculating the final class
grade uses the values
in cells B12, C12,
and D12. When you
copy the Final Grade
formula from the first
row (cell E2) to the
rows below it, Excel
offsets the formula
to use B13, C13, and
D13—none of which
exist. Thus a problem
happens—shown here
as a divide-by-zero
error. To fix this, you
need to use absolute
cell references.
Fortunately, Excel provides a perfect solution. It lets you use absolute cell references
cell references that always refer to the same cell. When you create a copy of a formula
that contains an absolute cell reference, Excel doesn’t change the reference (as it does
when you use relative cell references; see the previous section). To indicate that a cell
reference is absolute, use the dollar sign ($) character. For example, to change B12
into an absolute reference, you add the $ character twice, once in front of the column
and once in front of the row, which changes it to $B$12.
Here’s the corrected class grade formula (for Edith) using absolute cell references:
=B2/$B$12*25% + C2/$C$12*25% + D2/$D$12*50%
This formula still produces the same result for the first student. However, you can
now copy it correctly for use with the other students. To copy this formula into all
the cells in column E, use the same procedure described in the previous section on
relative cell references.
Partially Fixed References
You might wonder why you need to use the $ character twice in an absolute
reference (before the column letter and the row number). The reason is that Excel lets
you create partially fixed references. To understand partially fixed references, it helps
to remember that every cell reference consists of a column letter and a row number.
Search JabSto ::




Custom Search