Understand Boolean Logic: False Is Zero

Challenge: You want to become a guru at Excel formulas. To master conditional computing formulas, you need to understand Boolean logic facts.

Let’s say that you need to design a formula to calculate a 2% bonus. The bonus is paid if revenue is > 20,000 and gross profit percentage is > 50%. The bonus is also paid whenever the sales rep name is Joey.

If you only needed to see whether the revenue is greater than 20,000, the formula would be:

=IF(C4>20000,0.02*C4,0)

When you add in the additional condition that GP% needs to be > 50%, the formula is:

=IF(AND(C4>20000,D4>0.5),0.02*C4,0)

Add in the wrinkle that Joey always get paid, and you have:

=IF(OR(AND(C4>20000,D4>0.5),B4="Joey"),0.02*C4,0)

Figure 14.  A nonzero number used as a logical test is TRUE

Any time a formula calls for a logical test, you can include a calculation that generates a number. If the resulting number is 0, the logical test is FALSE. If the resulting number is anything else, the logical test is TRUE. In Figure 14, column A contains several numbers. Column B tests whether column A is TRUE or FALSE. You can see that all positive and negative numbers are TRUE, and the 0 in A4 is considered FALSE.

Using the Excel logic rules, you can restate any AND function by simply multiplying the logical tests together. To do so, you surround each logical test in parentheses. For example, you could rewrite: 

=AND(C4>20000,D4>0.5) as:

=(C4>20000)*(D4>0.5)

In Figure 15, column E shows the results of the latter formula.

Figure 15. If your operation is AND, multiply the various logical tests together.

The result of this calculation is always 0 or 1. It is 0 when the bonus should not be paid, and it is 1 when the bonus should be paid. Thus, if all your terms need to be joined by an AND, you can simply multiply the terms by the bonus calculation:

=(C4>20000)*(D4>0.5)*0.02*C4

Figure 16 shows the results of this calculation.

Figure 16.  You can multiply the bonus calculation by the results of your AND operations.

Excel’s Boolean logic rules run into some problems when you introduce OR operations.

When you convert an OR to an addition operation, there is a chance that the result might be > 1. Figure 17 shows this. You can restate this formula:

=IF(OR(AND(C4>20000,D4>0.5),B4="Joey"),0.02*C4,0)

as:

=((C7>20000)*(D7>0.5))+(B7="Joey")

In row 7, Joey actually qualifi es for a regular bonus, so the result of the logical test is 2. It would be incorrect to multiply the revenue by 2%. (Actually, Joey’s dad might like this idea.…) Any time you have an OR in the equation, you have to convert the result to TRUE or FALSE, or 0 or 1. Either of these formulas would calculate the bonus correctly:

=IF((C4>20000)*(D4>0.5)+(B4="Joey"),TRUE,FALSE)*C4*0.02

=IF((C4>20000)*(D4>0.5)+(B4="Joey"),1,0)*C4*0.02

Figure 17.  You can convert OR functions to addition and test whether the result is TRUE.

Summary: Understanding the Boolean logic facts can simplify your IF calculations. Remember that FALSE is 0, AND is *, OR is +, and everything else is TRUE.