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
of a stock
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:
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.