IF with Two Conditions

March 21, 2022 - by Bill Jelen

Problem: The CFO decided we should only pay the 2% bonus if a second condition is met. The GP% must be 50% or higher in addition to the sale being over $20,000.

Strategy: There are three common solutions to this problem: nesting IF statements, using AND, using boolean formulas. All three will be discussed here.

The most common solution is nesting one IF statement inside of another. The formula would be: =IF(F2>20000,IF(I2>0.5,0.02*F2,0),0). This first checks if the revenue is over $20,000. The second argument holds a formula to use when the logical test is true. In this case, the second argument is another IF statement that checks to see if the GP% is over 50%.

With two conditions, the temptation is to nest two IF functions, one inside the other. — Figure 385. Using a second IF statement as the second argument.

Gotcha: Don’t forget to type the ,0) at the end of the formula. This will provide the third argument and closing parentheses for the first IF statement.

Imagine if you had to test for five conditions. The above approach becomes unwieldy, as the formula is =IF(Test1,IF(Test2,IF(Test3,IF(Test4,IF(Test5, Formula If True,0),0),0),0),0). Using AND will simplify the calculation.

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.

Use the AND() function as the logical test inside the IF statement. =IF(AND(F2>20000,I2>0.5),0.02*F2,0).

Instead, load both conditions into an AND function and use that as the first argument to IF. — Figure 386. Using AND simplifies the IF statement.

If you see the power of AND, then you will appreciate the OR and NOT functions. The OR function 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. Students of logic design might remember that when you algebraically simplify a complex boolean expression, using NOT(OR()) might be the simplest way to create a test.

I’ve done my Power Excel seminars for thousands of people who use Excel 40 hours a week. 70% of those people suggest using multiple IF statements. 29.9% of those people suggest using AND. Only one person has ever suggested the following clever method.

This formula starts out calculating a 2% bonus for everyone: =F2*0.02. But then the formula continues with two additional terms. =F2*0.02*(F2>20000)*(I2>.5). Those additional terms must be in parentheses. Excel treats (F2>20000) as a logical test and will evaluate that expression to either TRUE or FALSE. As Excel is calculating the formula, one intermediate step will be =22810*0.02*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. Since any number 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. =22810*0.02*1*0 becomes 0. In row 7, =21730*0.02*1*1 becomes $434.60 and a bonus is paid.

Or, go Boolean. Revenue times bonus amount times Condition 1 in Parentheses times Condition 2 in Parentheses. — Figure 387. Multiplying by a logical test is equivalent to AND.

Additional Details: Excel treats TRUE as a 1 when you use an operator such as +-*/^ on the TRUE value. This does not happen when Excel is calculating functions. If you enter =SUM(A1:E1) and cells in that range contain a TRUE, the TRUE is ignored.

Gotcha: Don’t use this last method when you have an OR condition. Traditionally, AND is equivalent to multiplication and OR is equivalent to addition. While the multiplication concept works fine in Excel, the addition will end up paying a double-bonus: =F2*0.02*((Test1)+(Test2)) might end up with =F2*0.02*2 which is not what you want.

This article is an excerpt from Power Excel With MrExcel

Title photo by Nick Fewings on Unsplash