Microsoft Excel: Learn To Use Boolean Logic Facts To Simplify Logic

Problem: If you have to enter multiple IF conditions, having a good understanding of logical operators will help you to simplify the formula.

See all Microsoft Excel tips

Strategy: When you are dealing with conditions, the language is full of And, Or, Not, Nor, True, and False. All of these words have mathematical equivalents. Understanding them will enable you to build complex two-condition formulas.

A Boolean formula returns either TRUE or FALSE. In Fig. 590, the formula

=A2>100 will return TRUE.

You can have many such tests. As shown in Fig. 591, this dataset has columns to test if the product is a particular product line or if the region is a particular region.

You can build a calculation from the results of multiple Boolean formulas. One popular operator in Boolean logic is the AND operator. If you want to know if D2 AND E2 is TRUE, you can state this as a formula.

In Boolean Logic,

• Think of each TRUE as the number 1.

• Think of each FALSE as the number 0.

• Think of each AND as a Multiplication Operator

• Think of each OR as an Addition Operator

If the result of the calculation is 0, then the answer is FALSE. If the result of the calculation is non-zero then the answer is TRUE.

Here is an example, as shown in Fig. 592:

Plain language: The bonus is paid if the sale is >100 and the product is ABC.

Excel: =(A2>100)*(B2=“ABC”)

A=105 B=ABC: TRUE * TRUE= 1*1=1=TRUE

A=92 B=ABC: FALSE * TRUE= 0*1=0=FALSE

A=85 B=DEF: FALSE * FALSE= 0*0=0=FALSE

A=101 B=DEF: TRUE * FALSE= 1*0=0=FALSE

Here are the logic rules for AND operators and OR operators.

AND OR

TRUE*TRUE=TRUE TRUE+TRUE=TRUE

TRUE*FALSE=FALSE TRUE+FALSE=TRUE

FALSE*TRUE=FALSE FALSE+TRUE=TRUE

FALSE*FALSE=FALSE FALSE+FALSE=FALSE

Here is another example to work through.

Plain language: Bonus is paid for selling any item over $100.00 or for sales of DEF product.

Excel: =(A2>100)+(B2=“DEF”), as shown in Fig. 593.

Sales Product S ales>100 I tem=DEF Bonus Calculation

80 DEF FALSE TRUE =0+1=1=TRUE

105 DEF TRUE TRUE =1+1=2=TRUE

90 ABC FALSE FALSE =0+0=0=FALSE

110 ABC TRUE FALSE =1+0=1=TRUE

Using the above rules, you can write complex sets of Boolean logic. The formula in Fig. 594 would pay a $25 bonus for all West region sales of jackets at any price or caps above $50.

Summary: Excel does offer the AND and OR functions. However, being able to use Boolean terms as the first parameter of an IF statement allows for more complex calculations.

Functions Discussed: =IF(); =AND(); =OR()