Datasheet

23
Chapter 1: Evaluating Data in the Real World
= D2 + E2 + F2 + G2
After autofill, the formula in H3 is
= D3 + E3 + F3 + G3
and the formula in H4 is . . . well, you get the picture.
This is perfectly appropriate. I want the total in each row, so Excel adjusts
the formula accordingly as it automatically inserts it into each cell. This is
called relative referencing — the reference (the cell label) gets adjusted rela-
tive to where it is in the worksheet. Here, the formula directs Excel to total up
the numbers in the cells in the four columns immediately to the left.
Now for another possibility. Suppose I want to know each row total’s propor-
tion of the grand total (the number in H11). That should be straightforward,
right? Create a formula for I2, and then autofill cells I3 through I10.
Similar to the earlier example, I’d start by entering this formula into I2:
=H2/H11
Press Enter and the proportion appears in I2. Position the cursor on the fill
handle, drag through column I, release in I10, and . . . D’oh!!! Figure 1-7 shows
the unhappy result — the extremely ugly #/DIV0! in I3 through I10. What’s the
story?
Figure 1-7:
Whoops!
Incorrect
autofill!
05 454060-ch01.indd 2305 454060-ch01.indd 23 4/21/09 7:17:59 PM4/21/09 7:17:59 PM