It's not the most complex spreadsheet challenge, but working out weighted averages is a perennial favourite on Any Answers. Simon Hurst explains how to do it.

Apparently, one of the most frequent queries about Microsoft Excel concerns the formula for calcuating weighted averages. Indeed, an Any Answers question [1] on the subject has generated over 20,000 views.

Before I start, I should admit that I am neither a mathematician nor a statistician, so what follows is my understanding of weighted averages and averages in general. If you know better, or could contribute some useful information, then please do add your thoughts at the end of the article.

In summary, a weighted average is the average of a series of values each of which is multiplied by a 'weighting' value. The weighted average is calculated by multiplying each value by its weighting, and then dividing the sum of the totals by the sum of the weights. So, to take a simple example, if we had three items and we knew the sales value per item, and the number of each item sold, we could calculate the average sales value as (item1 value * number of item1s sold) + (item2 value * number of item2s sold) + (item3 value * number of item3s sold) all divided by the total number of items sold:

In this case, the weighted average is little more than a shortcut to finding the simple average. We would get the same result if we listed the value of every single sale separately and divided it by the total number of sales.

There are situations where the idea of a weighted average is more important. For example, we might know the total value of sales of items 1,2 and 3, but we might sell the same items for different amounts and not have access to the individual sales figures. In this case we would be unable to calculate the average on the basis of individual sales values divided by total sales, so would have to use the weighted average. We might also allocate a weighting based on something other than the quantity of results. For example, we might have conducted a survey using a variety of methods and believe that certain methods were more reliable than others – we could therefore allocate a weighting based on our assessment of reliability, so that more reliable results counted for more in our final analysis.

Whatever the reason for the use of a weighted average, the maths is the same: the sum of values multiplied by weighting, divided by the sum of weighting.

Achieving this in Excel is straightforward if we include a column for the total of the calculations of individual item times weighting. We can just sum this column and then divide the total by the sum of the weighting column, avoiding the use of anything more complicated in Excel than the SUM() function and multiply and divide:

The Any Answers article looked at achieving this without any totals column and in a single formula. The key to this is the concept of an 'array' formula – a formula that includes a reference to one or more 'blocks' of cells and processes each of those cells individually. One of our early Fun with Functions articles looked at simple array formulae [2].

In this example, if we want to calculate our weighted average in one formula, we need to be able to calculate our individual value times quantity totals in a single cell. Obviously in this example, with only three items, we could just use the SUM() function to total the individual calculations, but this would be impractical with dozens of items:

=SUM(B14*C14,B15*C15,B16*C16)

We could try:
=SUM(B14:B16*C14:C16)

But this will return a #VALUE error since a normal formula can't operate on a range (e.g. =B14:B16 will generate an error).

However, if we enter exactly the same SUM() formula but this time save it as an array formula by pressing Ctrl+Shift+Enter rather than just Enter, it will be transformed into an array formula and will operate as though we had entered all the calculations individually as in our first example:

Note the curly brackets or 'braces' around the formula. These are added automatically when you use Ctrll+Shift+Enter to create an array formula.
We could then include the division by the sum of the quantities to calculate our weighted average:

{=SUM(B14:B16*C14:C16)/SUM(C14:C16)}

Clever as array formulae can be, not everyone is familiar with how they work so they can be difficult to understand and, if left unprotected, very susceptible to being corrupted by users not using Ctrl+Shift+Enter to save them after editing.

Often a more straightforward approach is available by using one of the Excel functions that behave like an array formula, without needing to be entered as one. In the case of this calculation the obvious candidate would be SUMPRODUCT() which multiplies its arguments together. If those arguments contain blocks of cells, then the first cell in block 1 will be multiplied by the first cell in block 2, the second cell in block 1 by the second cell in block 2 and so on. Each block must contain the same number of cells. So our formula could be written as:

=SUMPRODUCT(B14:B16,C14:C16)/SUM(C14:C16)

With no need to worry about using Ctrl+Shift+Enter.

The Fun with Excel functions series
1. OFFSET [3]
2. ABS, SUMIF and the array formulae [4]
3. Expenses tracking tutorial for Excel 2007 [5]
4. Dealing with errors and the new Excel 2007 IFERROR() function [6]
6. Use Excel's MOD() function to allocate quarterly payments [7]

Also see:
ExcelZone Compendium - Practical tips for using SUMIF() and IF() [8]
ExcelZone Compendium: Functions & Formulae [9]

Subscribe to the ExcelZone newswire
[10]To keep up with spreadsheet issues and regular tutorials, click the button below to subscribe to the free monthly ExcelZone newswire. The subscribe function [11] will take you back to the AccountingWEB.co.uk home page after it adds your name to the subscription list.