 Home Excel Access | Feedback | Simple averages | SumProduct | Full Index |

# Calculating a weighted average using Excel Functions

A common statistical technique to summarise a selection of values is the arithmetic mean - generally known as the average. If measuring the average price of foodstuffs you could take a list of products available and then calculate the average. E.g. Bread (60) + Milk (30) + Beef (300) + Caviar (5,000) = Average price (1,348). This is misleading however. The average cost of an item in your shopping basket is unlikely to be 1,348 because it would seem reasonable to adjust the values to reflect the relative importance of each item. Milk is more important because it is typically purchased more often than caviar. A retail price index would take this into account and use a weighted average which gives more prominence to commonly bought items.

Excel does not contain a built in function to calculate a weighted average. It is however easy to do it using the SUMPRODUCT() function in a simple formula.

 - A B C 1 Weighted average 2 3 Cost Staff 4 Grade A 13000 5 5 Grade B 15000 2 6 Grade C 20000 3 7 8 Average 16000 9 Wtd Avg 15500

SumProduct() multiplies two arrays (or ranges) together and returns the sum of the product. In the illustration it would calculate '(B4 x C4) + (B5 x C5) + (B6 x C6)'. The formula in cell B9 is: = SUMPRODUCT(B4:B6, C4:C6) / SUM(C4:C6) The result shows that the weighted average is less than the plain arithmetic mean. This is because it has taken into account the larger number of staff being paid the lower salary.

 - F G H 13 Forecast incorporating risk 14 15 Probability Sales 16 Good weather 30% 10000 17 Mediocre weather 50% 8000 18 Poor weather 19% 2000 19 Hurricane 1% 0 20 21 Forecast 100% 7380

The weighted average can also be used for assessing the risk or determining the probability of various outcomes. If a judgement is made about the likelihood of various weather conditions for an outdoor sporting and the effect on ticket sales, a predicted value of sales can be calculated using a similar formula as the previous example. =SUMPRODUCT(G16:G19, H16:H19) returns the value of 7,380. The probability values (G16:G19) are already expressed as percentages (totalling 100% or 1.0) and so there is no need to divide by SUM(G16:G19).

The SUMPRODUCT function is much more versatile than is illustrated in the examples shown above. Click here for a fuller description. Get this information as a documentaccompanied by Excel worksheets Click here for details about obtaining thisfile. It has been rewritten for Excel 2010.

 file:xlwtdavg.htm Page last updated Nov17 © MeadInKent.co.uk 2017 CMIDX S1 P5 Y