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)}.
 
Search JabSto ::




Custom Search