Gideon’s Excel Tips, no. 6: Suppressing errors in an AVERAGE calculation using a single-cell Array formula
Array formulae: Excel black belt level!
Array formulae have a fearsome reputation, for several reasons. They can do magical things like sorting data sets dynamically and removing duplicates. They can be really hard to understand and, when they go wrong, really, really difficult to debug. They can be great for showing off and impressing your friends at Excel parties. But, as with most of Excel, if you learn things in the right order, anyone with a bit of patience can master them.
An example
Here is a very simple example of a use for an array formula that can’t be done any other way (unless you know better!) and that overcomes a fundamental problem we often hit.
The problem is that when we want to do an aggregate calculation like an average or a standard deviation, if the data contains error values, like #DIV/0 or #N/A, the error value will come through in the AVERAGE calculation.
Let’s say that we are running a small business selling cleaning products. Download the practice file here if you want to try it yourself. We have a table of sales data on the right and the prices are drawn from a separate lookup table on the left using VLOOKUP formulae in column H. We’d like to calculate the average sale value in cell I26 for the Sales in column I.
The problem is that the price lookup table is missing a row for the Kitchen spray cleaner, code KitSC40, and that gives rise to #N/A errors. We don’t have time right now to find the price for the missing product and decide to find the average of the Sales for which we do have price data. But if we just use an ordinary AVERAGE formula in cell I26, that will also show an #N/A error.
A workaround
We can get around this by doing the calculation in stages. We could add a new SalesNoErrors column in which we replace the error values by empty strings. This can be done using the IFERROR function that returns the first argument if it isn’t an error value and returns the second argument if it is.
In the example, =IFERROR(I5,"") returns I5 as long as it is not an error value. If I5 is an error value, it returns the empty string "" instead.
If we do this all the way down column J, calculating the AVERAGE of these cells will then give the result we want. (Note that we can’t replace the error values with zeros because these will be counted in the average calculation and make the result too small. We need to calculate the average of all those cells in which the price isn't #N/A. Replacing the errors by empty strings works because they are ignored in the average calculation.)
Using an array formula
It would be nice if we could do the calculation in one go rather than having to create a new column full of formulae. An array formula can do exactly that, working out the values with the errors replaced with empty strings in memory and then calculating the AVERAGE of the result.
Enter the formula =AVERAGE(IFERROR(I5:I25,"")) into cell I26, but to tell Excel that it is an array formula not just a normal formula, you must enter it by typing Ctrl+Shift+Enter. If you have done this correctly you will see that Excel has enclosed the whole formula in curly braces, like this:
{=AVERAGE(IFERROR(I5:I25,""))}
Don’t type the braces in. Excel will add them for you when you type Ctrl+Shift+Enter.
If we can do it that way, we don’t need all the calculations in column J.
Excel Masterclasses for aspiring power users
We are launching a range of Masterclasses for Management Consultants and other people who need a higher level of Excel skills than is available from mainstream training companies. The courses are intended for analysts and managers who need to become true Excel power users.
The full range of courses is outlined here.
The first course to be launched is a two-day course on the fundamental subject of Formulae. If you are interested in any of the subjects taught in our more specialised courses such as data visualisation, sensitivity analysis using probability distributions as inputs or what-if analysis, they you should really consider doing the formula course first. For more information on the Formula course or to book take a look here.
The courses will be run using webcasting technology in real-time and with small groups, so there will be plenty of interaction and lots of practical exercises. For the first course we will be using UK timings, starting at 9AM UK local time, but you are welcome to join from outside the UK, and the first course is scheduled for 13-14 June 2019. The course will be taught in English.