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
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.
Search JabSto ::

Custom Search