Page 118 - Excel Workbook for Dummies

11_798452 ch06.qxp 3/13/06 7:48 PM Page 101
Chapter 6: Copying and Correcting Formulas 101

1. Position the cell cursor in cell R10 and then select the cell range R10:R13.
You are going to construct the following array formula that you then enter as a
single entry into the cell range R10:R13 you just selected:
={A4:A7*R4:R7}
The range A4:A7 contains the hourly rates for all four employees, whereas the
range R4:R7 contains the total hours each one worked in the first period.
2. Type = to start the array formula and then select the cell range A4:A7.
3. Type * (asterisk), the multiplication operator, and then select the cell range
R4:R7.
To complete an array formula, you must press a very special key combination,
Ctrl plus Shift plus Enter. This key combination lets Excel know you’re construct-
ing an array formula as well as informs it to enter this array formula into all the
cells in the selected range.
4. Press Ctrl+Shift+Enter to complete the array formula and enter it into the
selected range R10:R13.
Excel responds by entering the array formula in all the cells in the selected range
R10:R13.
5. Construct an array formula in the cell range AI10:AI13 that multiplies the hourly
rates in the cell range A4:A7 by the second-period hour totals in the cell range
AI4:AI7.
The array formula you enter into the cell range AI10:AI13 appears on the Formula
bar as follows:
={A4:A7*AI4:AI7}
6. Construct an array formula in the cell range AJ10:AJ13 that totals the two pay
periods for each employee.
The array formula you enter into the cell range AJ10:AJ13 appears as follows on
the Formula bar:
={R10:R13+AI10:AI13}
7. Select cell AJ10 to deselect the cell range and then press the Delete key to
remove the addition formula from this cell.
Excel responds by displaying an alert dialog box informing you that you cannot
change part of an array. When editing array formulas, you must work entirely
with the ranges specified as the operands.
8. Select OK to close the alert dialog box and now select the range AJ10:AJ13 before
you press Delete.
This time, Excel deletes the array formula from the entire range.
9. Select Undo to restore the deleted array formula to the range AJ10:AJ13, and
then select cell A1 and save this workbook with the filename Solved6-5.xls in
your Chapter 6 folder in the My Practice Spreadsheets folder before you close
the workbook.

Tracing and Eliminating Formula Errors

As you found out firsthand when performing Exercise 5-3 in Chapter 5, an error value
returned by a single faulty formula can spread like wildfire to all the cells that refer to