Excel has the capability to calculate standard averages using the AVERAGE function. However, if you need to weight the average based on a particular quantity, there is no single Excel function that can handle this. Now, this doesn't mean that it can't be done. We just need to nest two Excel functions together, SUM and SUMPRODUCT. The SUMPRODUCT function is very handy when you have summary columns with dollar amounts and quantities. Essentially, it multiplies the quantity and the $ amount without you having to write a =(QuantityCell*DollarAmountCell) type formula. Now, this wouldn't really make sense if you just want to evaluate 2 cells. But, when you have to evaluate entire columns of data, this function shines. The SUM function does exactly what it says, it sums data cells.
So, to calculate a weighted average, what we need to do is multiply each $ amount by it's associated quantity, add all of them together, and then divide that amount by the total sum of the quantities. So, if we have quantities in range B3:B7 and $ amounts in range C3:C7 and we want to put the weighted average in C11, we would enter the following formula in C11, =SUMPRODUCT(B2:B6,C2:C6)/SUM(B2:B6).
As you can see in the example below, using the standard average formula results in a massive inflation due to that one company having such a high $ amt, when that $ amt only occurred 6 times. Now look at the weighted average version. It brings everything back into line the way it should be taking each companies quantity into consideration when calculating the average. I have also included a blown out version of the weighted average formula so you can better see what it is truly doing.
