Microsoft Office Tutorials and References

In Depth Information

**Absolute Cell References**

Copying Formulas

Figure 17-13:

When you paste the

formula into one or

more new cells, each

Final Grade formula

operates on the data

in its own row. This

means that you

don’t must tweak the

formula for each

student. The formula bar

shows the formula

contained in cell E3.

Absolute Cell References

Relative references are a true convenience since they let you create formula copies

that don’t need the slightest bit of editing. But you’ve probably already realized that

relative references don’t always work. For example, what if you have a value in a

specific cell that you want to use in multiple calculations? You may have a currency

conversion ratio that you want to use in a list of expenses. Each item in the list needs

to use the same cell to perform the conversion correctly. But if you make copies

of the formula using relative cell references, then you’ll find that Excel adjusts this

reference automatically and the formula ends up referring to the wrong cell (and

therefore the wrong conversion value).

Figure 17-14 shows the problem with the worksheet of student grades. In this

example, the test and assignment scores aren’t all graded out of 100 possible points;

each item has a different total score available (listed in row 12). In order to calculate

the percentage a student earned on a test, you need to divide the test score by the

total score available. This formula, for example, calculates the percentage for Edith

Abbott’s performance on Test B:

=B2/B12*100%

To calculate Edith’s final grade for the class, you use the following formula:

=B2/B12*25% + C2/C12*25% + D2/D12*50%

Like many formulas, this one contains a mix of cells that should be relative (the

individual scores in cells B2, C2, and D2) and those that should be absolute (the possible

totals in cell B12, C12, and D12). As you copy this formula to subsequent rows, Excel

incorrectly changes all the cell references, causing a calculation error.