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.