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.
