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