Part II: Working with Formulas and Functions
weight is 17%, and it is computed by dividing 72,021.35 by 423,655.02. The second argu-
ment is the rate of return.
FIGURE 16.1
An investment portfolio with rates of return
The dollar signs in the $C$8 reference cause that reference to be absolute rather than relative. See
Chapter 9 for a discussion of relative and absolute cell references.
SUMPRODUCT multiplies each element of the first argument by the corresponding element
in the second argument. The element C3/C8 is multiplied by D3, the element C4/C8 is mul-
tiplied by D4, and so on. When all five elements are multiplied, SUMPRODUCT sums the five
results.
If we used AVERAGE to find the simple average of the returns, we would get 5.906%. That’s
lower than our weighted average because investments such as the Sparkle Growth and
Income Fund have both a higher return than average and represent a larger proportion of
the portfolio.
Alternatively, all of the work that SUMPRODUCT does to compute the weighted average
could be done with simpler functions in adjacent cells. Figure 16.2 shows the same calcula-
tion, but rather than using SUMPRODUCT in one cell, each investment’s weight is calcu-
lated in its own cell, the effect of the rate on the whole is calculated, and those values are
summed.
