Simon Hurst explains how through logical functions such as IF(), AND(), OR(), NOT() and IFERROR() you can automate Excel functions, and explores alternatives to the use of IF().
There are many situations where an Excel calculation needs to return different answers based on the value in a different cell or different cells. IF() might be the most obvious function that achieves this, but the same process is used in a range of Excel functions, including the various conditional aggregation functions such as SUMIFS(), COUNTIFS() and AVERAGEIFS().
Since Excel 2016 was released, users with an Office 365 subscription have had access to two new Excel logical functions IFS() and SWITCH(). We will start off explaining how simple Excel logical functions work before examining some more advanced uses in part 2.
TRUE and FALSE
Logical functions depend on Excel statements that evaluate as TRUE or FALSE. Although most Excel calculations result in a text or number value, Excel will also evaluate a statement as TRUE or FALSE. If you enter =1=1 into a cell, Excel will return TRUE and =1=2 will return FALSE.
TRUE and FALSE can also be converted into numbers and numbers can be converted to TRUE and FALSE.
When Excel recognises that you wish to treat a value as a number, it will do its best to 'coerce' the value into its numeric equivalent. It might look odd, but if you refer to a cell containing a TRUE or FALSE value using a double minus sign, the value will be coerced into its corresponding number.
The first minus allows Excel to recognise that the value needs to be treated as a number and the second minus restores the sign of the value to its original positive or negative state.
We can also see that using a logical function such as AND() to evaluate a number value returns TRUE for non-zero values and FALSE for zero.
The IF() function takes three arguments. The first argument is a statement that returns a TRUE or FALSE value, or a number that can be converted to TRUE or FALSE. If the first argument evaluates as TRUE the function will return the second argument entered, and if the first argument is FALSE the function will return the third argument:
It's worth noting that there is no need to compare an existing TRUE or FALSE value to TRUE or FALSE, so =(1=1) is the same as =(1=1)=TRUE. Also, if the value of the first argument is any number value apart from zero it will be treated as TRUE.
IF() with multiple conditions
So far, we have looked at using IF() with a single statement, but sometimes we will need to consider more than one statement to make our decision. For example, perhaps we want to offer free delivery if a customer has reached a certain level of turnover and a minimum number of orders
We have given our threshold value cells Range Names to make our formula easier to understand. Because we need both of our criteria to be met, we use the logical function AND(). AND() takes up to 255 TRUE or FALSE statements and returns TRUE if all the statements are TRUE and FALSE if one or more of them is FALSE:
If we wanted to offer free delivery if just at least one of the conditions was TRUE we could use OR() instead. This will return TRUE if one or more of the statements are TRUE.
Although when working with numeric comparisons we can just reverse the comparison operator to cope with the opposite of a condition (<> for not equal to, rather than = for equal to; < for less than rather than > for greater than), particularly when working with text values, we might need to use the NOT() logical function to 'reverse' a criterion.
In this example, we have added a further condition to our delivery charge test. We offer free delivery to any customer whose code does not start with the letter A. We have added this condition to our OR() function:
Looking at row 10, we can see that customer B01 does not meet either or the numeric criteria, but does receive free delivery because the code does not start with the letter A:
Excel 2013 introduced an additional logical function that we will cover briefly for the sake of completeness. XOR() performs an 'exclusive OR' evaluation.
Like OR() and AND() this function accepts a series of separate TRUE and FALSE values. XOR() returns TRUE if an odd number of those statements are TRUE and FALSE if an even number of the statements are TRUE.
Perhaps the most common application of XOR() is to evaluate two conditions and only return TRUE if just one of them (it doesn't matter which) is TRUE.
The table below shows the comparison of using AND(), OR() and XOR() with different combinations of two logical values. Cell E4 shows the difference between OR() and XOR(). OR() returns TRUE because at least one of the values is TRUE whereas XOR() returns FALSE because an even number of the values is TRUE.
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.