A common requirement with Excel users is to calculate the average of a set of numbers. But it is important understand weighted average versus average in Excel. Which one are you being asked for?
Average in Excel
Average is simply the average of a set of numbers. You can either add up the numbers and divide by the count of the numbers or you can use the AVERAGE function.
As shown below if we want to get the Average sales price of sales people we can use the AVERAGE function and it gets to a number of 23.18. But is that a fair reflection of the average price of our sales for the month?
If I take the units sold and multiply them with this average selling price (534 x 23.18 ) I get an answer of 12 377.36 which is not the actual total sales we achieved for this period. Why would these numbers differ?
The reason is because when you only use the average selling price, each selling price is treated the same. So it is as if we sold 1 unit of each of these items.
But if you look at the numbers we actually sold more of the R35 items. So what is the impact on our ‘average’ when taking into account how many were sold in each price bracket i.e. how did the weighting affect it?
Weighted Average takes into account how many of each item was sold at a certain price. So in the same example, note that in column D we determine the total sales for each row. In order to determine the weighted average we can take the Total Sales number and divide it by the Total Units Sold to get a weighted average of R23.58
If we now take the units sold (534) and multiply by the weighted average (R23.58) we MUST get back to the correct total sales (12 591.75).
This is a good check for weighted average. Multiply the units by the weighted average and it MUST come back to the total sales.
Weighted Average function in Excel?
There is no explicit weighted average function in Excel, but there are functions that can make it easier. The one we use a lot is SUMPRODUCT.
As shown in the images above, in order to determine the weighted average we first have to determine the total sales which is achieved by taking the units sold and multiplying by the selling price for each sales person.
You could also do this in one cell by typing =B2*C2+B3*C3+B4*C4+B5*C5+B6*C6+B7*C7+B8*C8 as shown below. Note that in this case you are just asking for trouble. Did you click on the correct cells at the correct times, have you clicked on the same cell twice, are there new rows added. All this can cause this number to be incorrect. So if you use this method you must do the reverse calculation and make sure that units x price comes to the correct number.
SUMPRODUCT for Weighted Average
This is where the SUMPRODUCT function can be useful. As shown below it is extremely simple. You just tell it which columns to look at (in this case cells B2 to B8 and C2 to C8). It then knows that you want to have B2 multiplied by C2 and added to the result of B3 multiplied by C3 etc.
In fact if you look in the Function Wizard (in the red box below) you will see exactly how it does it. This is however why it is so important that you correctly line up the columns or rows. They should contain exactly the same number of cells.
Notice as well that although we have only used 2 arrays, you can do more (if you click on the 3rd a 4th appears etc). So if your total is determined by for example
- the number of packs times
- the number of items in a pack times
- the price per item times
- the applicable tax rate for that item,
you can still use the SUMPRODUCT function.