Until I learned this trick, I used to calculate weighted averages in Excel the long way as shown below:
(Follow along in the Weighted_Average file if you like):
In the above example, we are looking to calculate the weighted average shrinkage across all product lines. The steps in the long hand way are:
Multiply the pounds times shrinkage for each product
Add the sum of these extensions
Divide the total extension by the total pounds
Maybe not so bad in this short example. But suppose you had a list of hundreds or thousands of items, such as each batch of product for the year?
Fortunately, there is a quicker way. Take a look:
Here we do it in one calculation, without doing all the extensions one by one. The formula is {=SUM(B2:B6*C2:C6)/B7} . Let's break it down, going from the inside out:
=B2:B6*C2:C6 We multiply the pounds times the shrinkage
=SUM(B2:B6*C2:C6) Now we add up the sum of these extensions
=SUM(B2:B6*C2:C6)/B7 Then we divide by the total pounds to get the weighted average
{=SUM(B2:B6*C2:C6)/B7} Last we add the brackets around the formula, otherwise, Excel will give you an error (#Value).
The last part is not intuitive, but not too hard. You follow steps 1-3. You would think you are done and ready to click Enter. Not so fast. You need the brackets. You get them by holding the Ctrl and Shift keys with one hand and clicking Enter with the other (Ctrl + Shift + Enter).
In technical terms, this last step tells Excel you are working with an array, so it knows how to do the calculation.
If you forget and just click Enter, you will get the #Value error. No problem. It is an easy fix. Go back to the cell and get in Edit mode. No change is needed, just click (Ctrl + Shift + Enter) and the brackets will appear and your formula should work.
You may not ever do weighted averages much, but when you do, this is a neat shortcut. Use it and dazzle your friends!