Weighted Average

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:

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:

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!

 

Return To Excel Tips Index