Microsoft Office Tutorials and References

In Depth Information

**Using Arrays in Formulas**

2. Position the cursor in the cell where you want the result to appear.

3. Enter
=SUM(
to start the function.

Note how a brace has
not
been entered in this step.

4. Click the first cell in the first list, and while holding the left mouse

button down drag the pointer over the first list. Then release the

mouse button.

5. Enter the multiplication sign (
*).

6. Click the first cell of the second list, hold down the left mouse button,

and drag the pointer over the second list.

7. Release the mouse button.

8. Enter a
).

9. Press Ctrl + Shift + Enter to end the function.

Do not just press the Enter key by itself when using an array with the

SUM function.

Array functions are useful for saving steps in mathematical operations.

Therefore you can apply these examples to a number of functions, such as

AVERAGE, MAX, MIN, and so on.

As another example, suppose you run a fleet of taxis and you need to

calculate the average cost of gasoline per mile driven. This is easy to calculate for

a single vehicle. You just divide the total spent on gasoline by the total miles

driven, for a given period of time. The calculation looks like this:

cost of gasoline per mile = total spent on gasoline รท

total miles driven

How can you easily calculate this for a fleet of vehicles? Figure 3-5 shows

how this is done. The vehicles are listed in Column A, the total miles driven

for the month appear in Column B, and the total amounts spent on gasoline

appear in Column C.

One single formula in cell C21 answers the question. By using the AVERAGE

function in an array formula, the result is returned without the need for any

intermediate calculations. The formula looks like this: {=AVERAGE(C6:C17/

B6:B17)}.