Calculating weighted averages in Excel

The question of how to get a weighted average or weighted mean in Excel arises frequently among new investors. This is largely due to the importance of weighted averages in answering many of the new investor’s questions such as the overall yield of a portfolio.

The investor will of course turn to his most handy and reliable tool in analyzing his portfolio namely Excel. Upon discovering that there is not a single function to calculate the value needed, many end up taking a circuitous route to arrive at the answer needed. While Excel does not have a weighted average function per se it does indeed have the components needed to render the process quite simple.  Below we will walk through the process step by step. I often find when searching for simpler methods of accomplishing tasks that it is helpful to start with an answer derived the long way and then that answer can be used to prove out the new method. I will proceed in that manner here by first providing an example with long hand solution and then demonstrating how to simplify the steps via Excel.

A weighted average is the sum of the products of the weights and values and then that sum is divided by the sum of the weights. Since it is so difficult to express even simple mathematical expressions in words it is better to reference the general form of the calculation.	General form
The presence of the summation symbol has been known to cause anxiety to some so let’s rewrite it as here which is the same thing.

Now that it’s established no math beyond multiplication and division is needed we shall proceed with an example. Consider a simple portfolio comprised of 3 stocks that have the market value and current yields show in the following table. The yield for the portfolio as a whole may be derived by using the weighted average of the yields.	Stock Market Value ($) Current Yield (%) A 100 4.0 B 50 3.5 C 100 2.0
Substituting the example values into the formula gives the image to the right and solving the math results in a value of 3.1 percent yield.

To solve the same example in Excel enter the values into a group of cells as shown here.
Excel provides the SumProduct function which will be used to take the sums of the products of market value and current yield. This solves the top line of our formula. Note the arguments to the function will be 2 seperate arrays and not one. Observe the next image
As previously stated the sum product of the two rows gives us the top line of the equation and all that remains is to divide by the sum of the weights. In this case the sum of the market values. The total formula then becomes =sumproduct(b2:b4,c2:c4)/sum(b2:b4) as in image to right.

Hit enter and note that value 3.1 is the result.

That’s it, all there is to doing a weighted average in Excel. Just take the sumproduct of the weights and values and divide by the sum of the weights. While this example is incredibly simple there isn’t much to add to it other than just increasing the number of values being used which is meaningless in terms of how to solve. Enjoy.