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 proﬁ 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