Excel Essentials:How can I test for two or more conditions in Excel?
How can I test for two or more conditions in Excel.
We have the following situation
The Salespeople will now, only get a bonus if there sales revenue is over €4000 but also the units sold must be over 80
There are a few ways we can tackle this problem.
By using
- Nested IF Function.
- The AND Function
- Boolean formulas.
We will look at each solution in turn.
Using Nested IF Function:
=IF(B5>=$C$1,IF(C5>80,B5 * $c$2,0),0).
This first condition checks if the revenue is over €4000. The second argument causes a action when the logical test is true. In this case, the second argument is another IF statement that checks to see if the units sold are over 80. If not, o is placed in the cell –the action that initiated if the condition is false.
Now let’s say you had to test for six conditions
The formula would be
=IF(Test1,IF(Test2,IF(Test3,IF(Test4,IF(Test5,IF(Test 6,
Action if If True,0),0),0),0),0),0)
This is all getting very complicated and prone for errors. A clearer way to test for more than one condition is to use the AND function.
AND Function:
The AND function will hold up to 255 logical tests. Separate each test with a comma. The AND function will return TRUE if ALL of its arguments are TRUE. If any argument is false, then AND will return FALSE.
So the Formula now would be
=IF(AND(B5>=$C$1,C5>80),B5*$C$2,0)
Don’t forget the OR and the NOT Functions.
The AND Function magnifies the power of the IF Function by allowing you to check for hundreds of conditions. We can go even further by using the OR and NOT Functions.
The OR function also takes up to 255 logical tests. If any one of the tests is TRUE, then OR will return TRUE.
The NOT function will reverse a TRUE to FALSE and a FALSE to TRUE.
Using Boolean formulas
We could use this formula instead
=B5*$C$2*(B5>4000)*(C5>80)
Those additional terms must be in parentheses. Excel treats (B5>4000) as a logical test and will evaluate that expression to either TRUE or FALSE.
Thus the formula can be evaluated as
=5088*0.10*TRUE*FALSE.
When Excel has to use TRUE or FALSE in an calculation, the TRUE is treated as a one. The FALSE is treated as a zero.
This is an important concept in Excel, especially if you start to use Array formulae in Excel.
Now since any value times zero is zero, the logical tests at the end of the formula will wipe out the bonus if any one of the conditions is not true. =5508*0.1*1*0 becomes 0. The second formula evaluated to =4600*0.1*1*1 becomes €4600 and a bonus is paid.