Learn how to automate Excel calculations through the use of logical functions such as IF(), AND(), OR(), NOT() and IFERROR(). Simon Hurst also explores alternatives to the use of IF().
In part one of the series we looked at the use of TRUE and FALSE values in Excel and their use to automate decision making using IF() and the other logical functions, such as AND() and OR().
Part two will consider some of the other Excel functions that rely on the evaluation of TRUE and FALSE values, including those that deal with calculations that might return error values, and the range of functions that summarise ranges of values depending on one or more criteria.
Functions for handling errors
Certain operations in Excel might return an error message rather than a normal value. Sometimes you will need to replace that error message with a text or number value. The Excel lookup functions are a good example.
If you are using VLOOKUP() or MATCH() to find a matching value in a separate table, you might just want to return the value 0 if no match is found. Just using the function itself will return the #N/A error message.
In the following example we have a Table of invoices and a Table of Freight charges. We want to show the freight charge applicable to an invoice by using VLOOKUP() to find the corresponding invoice number in the FreightCharges table:
Where there is no freight charge we just want to return a zero, but our VLOOKUP() formula returns the #N/A error message.
The ISERROR() function can identify that a formula returns an error:
We use our VLOOKUP() formula as the argument for the ISERROR() function and it returns TRUE if the formula returns any error, and FALSE if there is no error. As we saw in part one, we can use the IF() function to return different values for FALSE and TRUE statements:
Here, we are using our ISERROR() formula as our first IF() argument and then returning the value 0 if the statement is TRUE. If there is no error, so that the statement returns FALSE, we use our second argument which is the same VLOOKUP() function repeated.
Although ISERROR() does achieve our aim of replacing the #N/A errors with 0, because ISERROR() is triggered by any error, not just #N/A, it can mask errors that we weren't anticipating.
For example, if we had spelled VLOOKUP() incorrectly, our formula would return 0 for all of our results, perhaps leading us to believe that there were just no matches:
For this reason, it would be better to use the more specific ISNA() function which is only triggered by a #N/A error, with other errors still returning FALSE and therefore the overall results showing the error message:
Although ISERROR() and ISNA() can achieve the result we require, the formula structure has to include identical VLOOKUP() functions twice: the first one to check whether the function returns an error and the second one to calculate the actual result of the function if it does not. Excel 2007 introduced a new function that avoids this repetition: IFERROR().
IFERROR() combines IF() and ISERROR() and only requires two arguments. The formula to be evaluated is entered as the first argument and the value to be used if that formula returns an error, as the second argument.
If there is no error, the result of the first argument formula is returned. We have corrected the spelling mistake in VLOOKUP() to demonstrate IFERROR():
Of course, IFERROR() suffers from the same issue as ISERROR(): it would mask unexpected errors as well as handling expected errors.
Excel 2013 introduced the IFNA() function to address this issue. Just like IFERROR() it combines the specific error check function with IF() and only requires two arguments:
Conditional summary functions
One common use of comparisons resulting in TRUE or FALSE is in the range of functions that can summarise the values in a block of cells by reference either to the values themselves, or to values in related columns or rows. Prior to the introduction of Excel 2007, there were three such functions:
The COUNTIF() function needs just two arguments: the range of cells to be counted and a reference or expression that compares each to a value. Cells from the range are included in the resulting count value if the comparison returns TRUE.
SUMIF() and AVERAGEIF() can work with two arguments where the comparison is based on the same cells as the summary range, but a third argument can be used to based the comparison on values in a different range.
The criteria can be just a value if you want to include all items equal to that value, or you can use the greater than or less than operators. In the following example we have used greater than or equal to (">=") and less than or equal to ("<=") and combined these with a cell reference using the & operator to concatenate the comparison operator with the reference:
In the case of the SUMIF() and AVERAGEIF() functions we have used all three arguments as we are basing our condition on the Date column, but summarising the Value column:
From IF to IFS
Excel 2007 introduced enhanced versions of our three summary functions:
More recently, the Office 365 subscription version of Excel 2016 introduced two further functions:
All of these new functions extend the capabilities of the previous conditional summary functions by allowing the entry of multiple criteria range and criteria pairs.
This allows either the use of criteria based on multiple columns, and/or multiple criteria based on the same column, as in our examples below:
Although, in most cases, the conditional sum functions will be adequate to calculate the summaries needed, there are alternative methods, such as the use of Excel array formulae and the use of the SUMPRODUCT() function, that can create even more flexible summaries.
About Simon Hurst
Simon Hurst is the founder of technology training consultancy The Knowledge Base and is a past chairman of the ICAEW's IT Faculty.