Microsoft Office Tutorials and References
In Depth Information
UNDERSTAND BOOLEAN LOGIC: FALSE IS ZERO; AND IS *,OR IS + AND EVERYTHING ELSE IS TRUE
Boolean Values
Digital Values
Condition
A
Condition
B
A or B
Condition A
Condition B
A+B
FALSE
FALSE
FALSE
0
0
0
FALSE
TRUE
TRUE
0
1
1
TRUE
TRUE
TRUE
1
1
2
TRUE
FALSE
TRUE
1
0
1
Part
I
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 profi t 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
 
Search JabSto ::




Custom Search