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

314 Copying Formulas
Figure 1-7:
An original formula copied with the
Fill handle down the last column of the data table.
You can then use the Fill handle to copy this formula down the last column of the table to include the cell range by positioning the cell cursor in K4 and then dragging the Fill handle down to select K4:K7. If you were to then posi- tion the cell cursor in cell K5, you would see on the Formula bar that when Excel copied the original formula in cell K4 down to cell K5, it automatically adjusted the row reference to suit its new position so that the formula in cell K5 reads
=SUM(B5:J5)
Although at first glance it appears that Excel isn’t making exact copies of the original formula when it uses the relative cell addressing, that isn’t techni- cally true. Although the cell column references in the first example in Figure 1-6 and the row references in the second example in Figure 1-7 appear to
be adjusted to suit the new column and row position when you view the worksheet by using the R1C1 cell notation system, you’d actually see that, in R1C1 notation (unlike the default A1 system), each and every copy of the original formula is exactly the same.
For example, the original formula that I input into cell B8 (known as cell R8C2 in the R1C1 system) to sum the April sales for all the different part numbers reads as follows when you switch to R1C1 notation:
=SUM(R[–4]C:R[–1]C)
In this notation, the SUM formula is more difficult to decipher, so I will explain and then translate it for you. In R1C1 notation, the cell range in the SUM argument is expressed in terms completely relative to the position of the cell containing the formula. The row portion of the cell range expresses how many rows above or below the one with the formula the rows are. (Negative integers indicate rows above, whereas positive integers indicate rows below.) The column portion of the cell range in the SUM argument expresses how many columns to the left or right of the one with the formula