Page 109 - Excel Workbook for Dummies

11_798452 ch06.qxp 3/13/06 7:48 PM Page 92
92 Part II: Using Formulas and Functions

1. Enter the heading % of Total in cell A9 of the 2006 Prod worksheet. Now position
the cell cursor in cell B9 and use AutoFit to widen column A so that this new
heading is completely displayed in its cell.
2. Construct a formula in cell B9 that divides the April 2006 quota in cell B7 by the
projected total in cell K7 and then format the cell with the Percent Style number
format with two decimal places.
The formula in this cell should read:
=B7/K7
3. Use the Fill handle to copy the original formula you constructed in cell B9 to the
cell range C9:J9.
Something is clearly wrong with the copies, as they all contain those dreaded
#DIV/0! error values rather than the desired percentages.
4. Position the cell cursor in cell C7 to examine the formula that Excel copied there.
The formula in this cell reads:
=C7/L7
When Excel made the first copy, the program not only adjusted the column refer-
ence for the monthly quota (from B7 to C7) but for the projected total as well (J7
to L7), and because L7 is empty, the copy is, in essence, trying to divide by zero
(all empty cells carry a zero value), thus the division error value.
To prevent this problem with the copies (and to correct it), in the original divi-
sion formula, the divisor, K7, with the projected total must be an absolute cell
reference (as in $K$7), so that it remains constant in all the copies in which Excel
naturally adjusts the dividend (that is, the amount to be divided) containing the
various monthly production quotas.
5. Position the cell cursor back in cell B9 with the original formula and then press
F2 to put Excel in Edit mode.
6. Press F4 to convert the cell reference from the relative K7 to absolute $K$7 and
then click the Enter button on the Formula bar.
7. Use the Fill handle to copy this edited version of the original formula in cell B9 to
the cell range C9:J9.
That’s more like it! This time all the formula copies return realistic percentages
in place of those awful division errors.
8. Position the cell cursor in cell A1 of the 2006 Prod worksheet and then save the
workbook with the filename Solved6-2.xls in your Chapter 6 folder inside the My
Practice Worksheets folder before you close the workbook.

Copying Formulas with Mixed References

Just as you’d expect, mixed cell references are those that mix relative and absolute
references in a cell. A mixed cell reference can either have the row reference absolute
and column relative (as in A$3) or the column reference absolute and the row relative
(as in $A3).

You can convert a cell reference you’re entering or editing in a formula to either type
of mixed reference by pressing F4 as follows: