Understand Boolean Logic: False Is Zero; And Is *,or Is + And Everything Else Is True

February 03, 2021 - by Bill Jelen

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

Background: For a brief time, I was planning on being an electrical engineer. While most of the Notre Dame electrical engineering curriculum and I did not get along, my favorite class was logic design. I learned how to reduce many decisions down to a series of wires, electricity, and tiny chips that could perform AND, OR, and NAND operations. I learned how to use Karnaugh maps to reduce a circuit down to the minimal number of chips. It was fascinating stuff, and it translates amazingly well to writing criteria in Excel.

With integrated circuits, a circuit is either on or off. On means TRUE, or 1. Off means FALSE, or 0. The table on the left is the truth table for a simple AND operation. While one approach is to memorize these rules, you can also convert a problem to a simple mathematical calculation, as in the table on the right, which converts TRUE to 1, converts FALSE to 0, and multiplies the two values. Notice that the results in A and B in the left table are equivalent to the results in A*B in the right table.

With integrated circuits, an OR gate accepts two or more incoming wires. If any of the incoming wires are on, the output wire is on. Again, you can memorize the facts in the table on the left below, or you can simply change the TRUE to 1, the FALSE to 0, and the OR sign to a plus sign, as in the table on the right. If the result is 1 or greater, the entire problem is TRUE.

Solution: How does this talk of integrated circuits apply to Excel? When you build IF functions that require multiple logical tests, you frequently string together many AND, OR, and NOT functions to achieve a result. These formulas can get unduly complex, and you can use Boolean logic facts to simplify them.

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. (Joey is the boss’s son.)

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 qualifies 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.

Title Photo: Elisey Vavulin at Unsplash.com

This article is an excerpt from Excel Gurus Gone Wild.