Page 108 - Excel Workbook for Dummies

11_798452 ch06.qxp 3/13/06 7:48 PM Page 91
Chapter 6: Copying and Correcting Formulas 91
You will note that each and every cell in this column contains the exact same
formula:
=SUM(RC[-9]:RC[-1])
Excel hasn’t really adjusted anything in any of the formula copies you made! It’s
all a convenient fiction (that’s fancy talk for a big lie) to say that Excel adjusts
the relative row and column references in formula copies because that’s the way
it appears to work when the program is in its normal row number, column letter
reference system. (Now that I’ve let you in on this little secret, just keep it under
your hat.)
7. Position the cell cursor in cell R7C2 (erstwhile B7) and use AutoSum to construct
a SUM formula that totals the part numbers scheduled to be produced in April,
2006.
This formula in R1C1 notation appears on the Formula bar as follows:
=SUM(R[-4]C:R[-1]C)
8. Use the Fill handle to copy this formula across the rest of the columns of the
table to the range R7C3:R7C10 (C7:J7).
Verify that each of the copies in all these cells contains the identical formula in
R1C1 notation as you constructed in cell B7, that is, R7C2.
9. Restore the old tried-and-true row number and column letter notation system by
removing the check mark from the R1C1 Reference Style check box on the
General tab of the Options dialog box.
10. Move the cell cursor across the cell range B7:J7, noting how Excel adjusts the
column reference (in standard notation) and then up and down the cell range
K3:K7, noting how the row reference changes.
11. Save this workbook under the filename Solved6-1.xls in your Chapter 6 folder
inside the My Practice Spreadsheets folder on your hard disk and then close the
workbook.

Copying Formulas with Absolute References

Absolute cell references, as their name implies do not change for nobody or nothing. A
truly absolute reference firmly roots both the row and row reference (as opposed to a
mixed reference that just plants either the row number or the column letter, but not
both). This type of reference comes in handy when you need to refer to a cell that con-
tains a value that acts like a constant that must remain unchanged in all formula copies.

Excel indicates that a cell reference is absolute by preceding both its column letter
and row number with a $ (dollar sign), the symbol denoting an absolute reference, as
in $A$1 or $BC$457. When building or editing a formula, you can convert a relative
cell reference that contains the insertion point simply by pressing the F4 key.

Try It

Exercise 6-2: Copying a Formula with Absolute Cell References
Open the Exercise6-2.xls workbook file in your Chapter 6 folder in the My Practice
Spreadsheets folder inside My Documents on your hard disk or on the Excel
Workbook folder on the workbook CD-ROM. This file contains a copy of the 2006
Production Schedule table with all the formulas you created and copied in Exercise
6-1 for totaling the units to be produced by month across the columns and by part
down the rows. In this exercise, you add a row of formulas that computes what per-
centage each monthly production quota represents of the projected nine-month total: