Microsoft Office Tutorials and References

In Depth Information

**Using array formulas**

This worksheet stores the previous sorting times in minutes and percentage target in cells

B2:B5 and C2:C5, respectively. The array formula to calculate the targets for each of the four

centers is
=B2:B5*C2:C5
, which, when entered into cells D2:D5 by pressing Ctrl+Shift+Enter,

would appear as
{= B2:B5*C2:C5}
.

To edit an array formula, you must select every cell that contains the array formula,

click the formula bar to activate it, edit the formula on the formula bar, and then press

Ctrl+Shift+Enter to re-enter the formula as an array formula.

TIP
Many operations that used to require an array formula can now be calculated by using

functions such as
SUMIFS
and
COUNTIFS
.

In this exercise, youâ€™ll create and edit array formulas.

SET UP
You need the FuelSurcharges workbook located in the Chapter03 practice file

folder to complete this exercise. Open the workbook, and then follow the steps.

1

If necessary, click the
Fuel
sheet tab to display the
Fuel
worksheet.

2

Select cells
C11:F11
.

3

Enter the formula
=C3*C9:F9
and then press
Ctrl+Shift+Enter
to add the formula

{=C3*C9:F9}
to cells
C11:F11
.

4

With cells
C11:F11
still selected, click the formula bar, edit the formula so it reads

=C3*C10:F10
, and then press
Ctrl+Shift+Enter
to change the array formula to

{=C3*C10:F10}
.

5

Click the
Volume
sheet tab to display the
Volume
worksheet.

6

Select cells
D4:D7
.

7

Enter the formula
=B4:B7*C4:C7
and then press
Ctrl+Shift+Enter
to add the

formula
{=B4:B7*C4:C7}
to cells
D4:D7
.

CLEAN UP
Close the FuelSurcharges workbook, saving your changes if you want to.