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