Datasheet

24
Part I: Statistics and Excel: A Marriage Made in Heaven
The story is this: unless you tell it not to, Excel uses relative referencing
when you autofill. So the formula inserted into I3 is not
=H3/H11
Instead, it’s
=H3/H12
Why does H11 become H12? Relative referencing assumes that the formula
means divide the number in the cell by whatever number is nine cells south
of here in the same column. Because H12 has nothing in it, the formula is tell-
ing Excel to divide by zero, which is a no-no.
The idea is to tell Excel to divide all the numbers by the number in H11, not
by whatever number is nine cells south of here. To do this, you work with
absolute referencing. You show absolute referencing by adding $-signs to the
cell ID. The correct formula for I2 is
= H2/$H$11
This tells Excel not to adjust the column and not to adjust the row when you
autofill. Figure 1-8 shows the worksheet with the proportions.
Figure 1-8:
Autofill
based on
absolute
referencing.
To convert a relative reference into absolute reference format, select the
cell address (or addresses) you want to convert, and press the F4 key. F4 is
a toggle that goes between relative reference (H11, for example), absolute
05 454060-ch01.indd 2405 454060-ch01.indd 24 4/21/09 7:17:59 PM4/21/09 7:17:59 PM