| | Feedback | Simple averages | SumProduct | Full Index | | ||
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 |
 |
Click here for details about
obtaining this file. It has been rewritten for Excel 2010. |
| file:xlwtdavg.htm |  |
Page last updated Nov17 | © MeadInKent.co.uk 2017 | CMIDX S1 P5 Y |