Page 333 - Excel 2013 All-in-One For Dummies

Copying Formulas 315
the columns are. (Positive integers indicate columns to the right, and nega- tive integers indicate columns to the left.) When a column or row in the cell range is not followed by an integer in square brackets, this means that there is no change in the column or row.
Armed with this information, my translation R1C1 form of this formula may just make sense; it says, “sum the values in the range of the cells that is four rows (R[–4]) above the current cell in the same column (C) down through the cell that is just one row (R[–1]) above the current cell in the same column (C).” When this original formula is copied over to the columns in the rest of the table, it doesn’t need to be changed because each copy of the formula performs this exact calculation (when expressed in such relative terms).
The original formula in cell K4 (R4C11 in R1C1-speak) appears as follows when you switch over to the R1C1 notation:
=SUM(RC[–9]:RC[–1])
It says, “sum the range of values in the cell nine columns to the left (C[–9]) in the same row through the cell that is one column to the left (C[–1]) in the same row.” This is exactly what all the copies of this formula in the three rows below it do, so that when Excel copies this formula it doesn’t change.
You can use the R1C1 notation to check that you’ve copied all the formulas in a spreadsheet table correctly. Just switch to the R1C1 system by selecting the R1C1 Reference Style check box in the Working with Formulas section on the Formulas tab of the Excel Options dialog box (File➪Options➪Formulas). Then move the cell cursor through all the cells with copied formulas in the table. When R1C1 notation is in effect, all copies of an original formula across an entire row or down an entire column of the table should be identical when displayed on the Formula bar as you make their cells current.
Absolute references
Most of the time, relative cell references are exactly what you need in the formulas that you build, thus allowing Excel to adjust the row and/or column references as required in the copies that you make. You will encounter some circumstances, however, where Excel should not adjust one or more parts of the cell reference in the copied formula. This occurs, for example, whenever you want to use a cell value as a constant in all the copies that you make of a formula.
Figure 1-8 illustrates just such a situation. In this situation, you want to build a formula in cell B10 that calculates what percentage April’s part production total (B8) is of the total nine-month production (cell K8). Normally, you would create the following formula in cell B10 with all its relative cell references:
=B8/K8
Book III Chapter 1
Building Basic Formulas