Microsoft Office Tutorials and References

In Depth Information

**Using Arrays in Formulas**

Figure 3-3 shows a very common way to do this. Column D contains formulas

to calculate the value of each stock in the portfolio. This is done by

multiplying the number of shares for each stock by its price. For example, cell D4

contains the formula =B4*C4. Cell D10 sums up the interim results with the

formula =SUM(D4:D8).

Figure 3-3:

Calculating

the value

of a stock

portfolio,

the

oldfashioned

way.

The method shown in Figure 3-3 requires creating additional calculations —

those in Column D. These calculations are necessary if you need to know the

value of each stock, but not if all you need to know is the value of the

portfolio as a whole.

Fortunately, alternatives to this standard approach exist. One is to embed

the separate multiplicative steps directly inside the SUM function, like this:

=SUM(B4*C4,B5*C5,B6*C6,B7*C7,B8*C8)

That works, but it’s bloated to say the least. What if you had 20 stocks in the

portfolio? Forget it!

Another alternative is the SUMPRODUCT function. This function sums the

products, just as the other methods shown here also do. The limitation,

however, is that SUMPRODUCT can only be used for summing. It cannot, for

example, give you an average.

In many situations such as this one, your best bet is to use an array function.

Figure 3-4 shows the correct result from using the SUM function entered as

an array function. Notice that the formula in the Formula Bar begins and ends

with a brace.