Statistical Analysis with Excel For Dummies. Joseph Schmuller
rel="nofollow" href="#fb3_img_img_7225794d-acba-58d6-95a0-132f9cbc385d.png" alt="Tip"/> A quick way to autofill is to click in the first cell in the series, move the cursor to that cell’s lower right corner until the autofill handle appears, and double-click. This works on both PCs and Macs.
On the iPad, you select a cell and dots appear in the cell’s upper left and lower right corners. These dots are called selection handles. Tap the cell to open a pop-up menu. On the pop-up menu, tap Fill, touch the lower right selection handle, and drag in the desired direction (in this case, downward) to populate the cells. (See Figure 1-11.)
FIGURE 1-11: Autofill on the iPad.
Referencing cells
Another important fundamental principle is the way Excel references worksheet cells. Consider again the worksheet shown in Figure 1-8. Each autofilled formula is slightly different from the original. This, remember, is the formula in cell H2:
= 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. You 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 relative 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 you want to know each row total's proportion 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, you 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-12 shows the unhappy result — the extremely ugly #/DIV0! in I3 through I10. What's the story?
FIGURE 1-12: Whoops! Incorrect autofill!
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 telling Excel to divide by zero, which is a no-no.
The idea is to tell Excel to divide all 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 dollar signs ($) to the cell ID. The correct formula for I2 is
= H2/$H$11
This line tells Excel to not adjust the column and to not adjust the row when you autofill. Figure 1-13 shows the worksheet with the proportions, and you can see the correct formula in the formula bar (the area above the worksheet and below the Ribbon).
FIGURE 1-13: Autofill, based on absolute referencing.
A Mac shortcut for this is Command+T.
Here’s how you do it on the iPad. After you enter a formula in this type of context, like
= H2/H11
iPad suspects what you’re up to and highlights the part you might want to work with a bit more — in this case, H11. Tap on that term to pop up a menu. Choose Reference Type from that menu to open the Reference Type menu, shown in Figure 1-14. Tap the desired reference type — in this case, the first one — and then proceed to autofill column I.
FIGURE 1-14: Changing from relative to absolute reference on the iPad.
Chapter 2
Understanding Excel's Statistical Capabilities
IN THIS CHAPTER
