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.
IF()
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:
=IF(B10>=0,"Asset","Liability")
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(AND(B7>=Turnover,C7>=NumberOrders),0,Delivery)
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:
=IF(OR(B10>=Turnover,C10>=NumberOrders,NOT(LEFT(A10,1)="A")),0,Delivery)
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:
XOR()
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.
You might also be interested in
Simon Hurst is the founder of technology training consultancy The Knowledge Base and is a past chairman of the ICAEW's IT Faculty.
Replies (9)
Please login or register to join the discussion.
Time spreadsheets were banned.
Why?
I'd say it was time that spreadsheet users, and those that employ and manage spreadsheet users, realised the necessity of putting some time and effort into learning how to use them properly. Although there are many situations when other solutions are preferable to spreadsheets, there are still things that spreadsheets do very well if used with care and understanding.
There's another thread where somone has said that it is time people started using updated software rather than spreadsheets, so my post was a friday flippancy.
I totally agree that the only reason why people do not get on with spreadsheets is that they don't learn how to use them properly
Thanks John. Glad that we’re in agreement.
Absolutely agree with you there Simon
Obvious -
They are either True or False!
But don't tell Conviviality Directors that!
I coerce TRUE and FALSE by multiplying by 1.
=A1*1
You don't have to coerce them to use them as values. If you multiple by the cell it will automatically be coerced into one or zero.
If A1 contains TRUE then =10*A1 will return 10.
If A1 contains FALSE then =10*A1 will return zero.
The use of the arithmetic operator, such as multiply, is indeed what 'coerces' TRUE and FALSE and, for example, numbers entered as text, to be treated as numbers. So, exactly as you show in your example, multiplying a number by a TRUE or FALSE value will cause it to treat the boolean value as 1 or 0. This can be a useful alternative to using conditional functions, e.g. if A1 contains TRUE or FALSE:
=IF(A1,A2,0)
could be written as:
=A2*A1