Pin Me

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

written by: Mr Excel•edited by: Michele McDonough•updated: 7/6/2011

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

  • slide 1 of 2

    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()

    See all Microsoft Excel tips

  • slide 2 of 2

    Images

    Fig. 590Fig. 591Fig. 592Fig. 593Fig. 594