Microsoft Office Tutorials and References
In Depth Information
UNDERSTAND BOOLEAN LOGIC: FALSE IS ZERO; AND IS *,OR IS + AND EVERYTHING ELSE IS TRUE
Part
I
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 qualiﬁ es 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   Search JabSto ::

Custom Search