Page 154 - Excel Timesaving Techniques for Dummies

29_574272 ch25.qxd 10/1/04 10:47 PM Page 139
139
Hurray for Array Formulas!
If you were to perform this computation with regular 3. Type * (asterisk), select the cell range A4:A7,
formulas, you would create the following master for- and then press F4 to make the range absolute
mula in cell R10: ($A$4:$A$7).
=R4*A4 When specifying the operations to be performed
between array ranges, you use the normal opera-
You would then copy this formula down to the range tor (+, -, *, /, ^). You make the second array range
R11:R13. Next, you would perform a similar proce- absolute so that when you copy the array for-
dure, creating this type of formula in cell AI10 and mula from range R10:R13 to AI10:AI13, Excel does
then copying it down to the range AI11:AI13. Finally, not adjust this range and continues to use the
you would create a master formula in cell AJ10 that hourly rates established in this range.
summed cells R10 and AI10 and then copy this for- 4. Press Ctrl+Shift+Enter.
mula down to the cell range AJ11:AJ13.
To complete the entering of the array formula in
Instead, you can accomplish the same thing with the entire selected range (rather than in just the
three array formulas. In the cell range R10:R13, you current cell), you must press Ctrl+Shift+Enter.
create the following array formula that calculates Excel then inserts the array formula enclosed in
the first-period wages for all the employees: braces in every cell in the selected range. (See
Figure 25-3.)
{=R4:R7*A4:A7}

Next, in the cell range AI10:AI13, you create this
array formula to compute the second-period wages
for everyone:
{=AI14:AI17*A4:A7}

Finally, in the cell range AJ10:AJ13, you create this
array formula to compute the monthly wages for all
the employees:
{=AJ4:AJ7*A4:A7}

To see how quick and easy it is to build these array
formulas, follow along with these steps:

1. Select range R10:R13. • Figure 25-3: Worksheet after entering the first array
formula in the cell range R10:R13.
Instead of selecting a single cell, you select the
entire range into which the array formula will be 5. Press Ctrl+C, select cell AI10, and press Enter.
entered.
Excel copies the array formula into the range
2. Type = (equal sign) and then select the cell AI10:AI13, adjusting the first range from R4:R7 to
range R4:R7. AI4:AI7, without affecting the hourly wage range,
The equal sign followed by the address of the $A$4:$A$7 (see Figure 25-4).
cell range R4:R7 appears in the first cell of the
range you selected in step 1.