Excel Yourself: The lore of averages

Excel Yourself

How to use a straightforward Excel function to deliver an accurate average.

Question
Is there an easy way to do a weighted average Year To Date calculation from a budget layout?

Answer
The SUMPRODUCT function (my favourite Excel function) provides the easiest solution.
When working with averages and percentages over time, you can’t always average the averages or average the percentages. You may need to take into account differences in the months. In Figure 1, the table shows budgeted monthly production figures with their associated reject percentage.

In this budget listing, the reject percentage is static in the first six months. A shutdown is scheduled for December when a major upgrade of the machinery is planned. This is expected to both increase the production capacity and reduce the reject percentage.

The reject percentage is expected to reduce progressively during the last six months. 

Volumes (’000s) in the first half of the year total 600, while they increase to 800 in the second half.

We don’t know what the budgeted reject volumes are. We could add a new row to calculate them, but in this case we can use the SUMPRODUCT function to calculate the full-year reject percentage. Because the volumes increased and the reject percentages dropped, we can’t simply average the monthly percentages in row 3. If we did the result would be 2.30%, which is incorrect.

We need to multiply each month’s percentage by its related production figure to provide the reject volumes.

Figure 1

We then need to divide that reject volume by the total production volume to arrive at the reject percent for the whole year.

While this may seem difficult for a single formula, the SUMPRODUCT function is designed to handle this type of calculation.

The name SUMPRODUCT means to add up the results of multiplications. In mathematical terms, product means multiplication. If you calculate the product of two numbers, you multiply them together. So the SUMPRODUCT function multiplies two ranges together and totals all the individual multiplication results.

The formula for cell N3 is: =SUMPRODUCT(B2:M2,B3:M3)/N2

The syntax for the SUMPRODUCT is reasonably simple: SUMPRODUCT(Range1,Range2)

Range1 and Range2 need to contain values and be the same length, that is, the same number of cells. The first cell in Range1 is multiplied by the first cell in Range2 and the result retained. The second cell in Range1 is multiplied by the second cell in Range2 and its result retained. This is repeated for each of the remaining cells in the ranges. The ranges can be vertical or horizontal, as long as they contain the same number of cells.

In our example, cell B2 is multiplied by B3 and the result retained, then C2 is multiplied by C3 and so on, all the way across, until finally cell M2 is multiplied by M3. The results of all 12 multiplications are then added up to provide the total number of rejects for the year. That value is then divided by the total production volume from cell N2 to provide the average reject percentage for the whole year. The answer is 2.27% (see Figure 2), slightly less than the raw average of the percentages themselves.

Figure 2

Year to Date calculations


In terms of calculating a Year to Date (YTD) budget reject percentage during the year, the calculation is more complex, but it is basically the same as above.

Figure 3 (on next page) shows the YTD reject percentage calculation for March. Cell B6 holds the number of months to calculate.

The formula in B7 in Figure 3 is:
=SUMPRODUCT(B2:INDEX(B2:M2,1,B6),B3:INDEX(B3:M3,1,B6))/
SUM(B2:INDEX(B2:M2,1,B6))

The three INDEX functions provide the end references for each of the three ranges used in the formula.

The syntax for the INDEX function is:

INDEX(Range,Row_Number,Column_Number)

Range can be a single row or column, or a two dimensional range.

Row_Number is a positive whole number that represents the row number to extract from within the Range.

Column_Number (optional) is a positive whole number that represents the column number to extract from within the Range.

The first INDEX function in the formula is INDEX(B2:M2,1,B6). If used by itself in a formula, for example, =INDEX(B2:M2,1,B6), it would return 125, the value in cell J2. J2 is the cell in the first row (Row_Number = 1) and ninth column (Column_Number = B6 = 9) in the range B2:M2.

Figure 3

The INDEX function is one of the few Excel functions that can return a value from a cell or a reference to a cell. By this I mean the formula INDEX(B2:M2,1,B6) can refer to cell J2, not the value 125. When you use the INDEX function on the right of the colon : as in our example, it forces Excel to return a reference.

When you use B2:INDEX(B2:M2,1,B6), Excel converts the INDEX result into a reference to cell J2 and creates a range reference B2:J2. This is used by both the SUMPRODUCT and SUM functions to perform the weighted average calculation for nine months.

When you convert the three INDEX functions to their respective references, the formula for the budgeted March YTD reject percentage becomes:
=SUMPRODUCT(B2:J2,B3:J3)/SUM(B2:J2)

Changing the number of months in cell B6 will automatically change the calculation to work for a new month.

The use of the INDEX function in this way is a powerful technique that is often used to create flexible ranges that expand and contract as required. You can use the INDEX function on both sides of the colon to create flexible range references. These types of ranges are commonly referred to as dynamic ranges.

Excel tip

When working on large spreadsheets it is often useful to see as much of the Excel grid as possible. There are two options: use Full Screen mode, which removes Excel’s ribbon and just displays the Excel grid. To turn on Full Screen mode, click the View ribbon tab and click the Full Screen icon. Press Esc to return to normal screen. An alternative is to press Ctrl + F1, which minimises the ribbon across the top of the screen. This leaves the ribbon tab names visible, but hides the options. Click the ribbon tab name to display the options. Pressing Ctrl + F1 again shows the ribbon. In Excel 2010 and later versions there is a small arrow icon on the left of the small blue help button (in the top right of the screen) that also minimises the ribbon. You can also right click the ribbon to minimise it.
 

Further reading

Advanced Excel Reporting for Management Accountants
By Neale Blackwood
Wiley, A$76.95

 
Excel can be a very effective tool for managers but many users do litle more than scratch the surface of its potential. Neale Blackwood, a CPA Australia trainer and regular INTHEBLACK contributor, has immense expertise in Excel, which he lays out with an eye to practical financial reporting.

His book assumes that readers have a good working knowledge of Excel; the goal is to turn that knowledge into a reporting model that is tailored to a company's circumstances.
Blackwood explains structural design issues and special functions, and moves on to charting techniques and report validations. He also provides two case studies, using different reporting periods to illustrate each theory. There is a companion website that contains many of the excel spreadsheets used as examples in the book.
 
Neale Blackwood CPA runs A4 Accounting, providing Excel training, webinars and consulting services. Questions can be sent to [email protected].
 
This article is from the August 2014 issue of INTHEBLACK.


How-to articles, sample spreadsheets and more

Excel yourself

Combine annual and monthly data in Excel with these simple steps


Making data relationships work

Easy steps to make data Relationships work in Excel


Excel Yourself

Easy tips for matching salary to job class in Excel