MeadInKent
| Feedback | Simple averages | SumProduct | Full Index |

Calculating a weighted average using Excel Functions

Would you like to learn more?

Buy a book

Excel 2010 Bible by John Walkenbach (with CD)

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 document
accompanied by Excel worksheets
Document is in PDF format Click here for details about obtaining this 
file. It has been rewritten for Excel 2010.

file:xlwtdavg.htm Open MeadInKent Facebook page Page last updated Jan14 MeadInKent.co.uk 2014 CMIDX S1 P5 Y