Microsoft Office Tutorials and References

In Depth Information

**UNDERSTAND BOOLEAN LOGIC: FALSE IS ZERO; AND IS *,OR IS + AND EVERYTHING ELSE IS TRUE**

Any time a formula calls for a logical test, you can include a calculation that

generates a number. If the resulting number is
0
, the logical test is
FALSE
. If

the resulting number is anything else, the logical test is
TRUE
. In Figure 14,

column A contains several numbers. Column B tests whether column A is
TRUE

or
FALSE
. You can see that all positive and negative numbers are
TRUE
, and

the
0
in A4 is considered
FALSE
.

Using the Excel logic rules, you can restate any
AND
function by simply

multiplying the logical tests together. To do so, you surround each logical test in

parentheses. For example, you could
rewrite:=AND(C4>20000,D4>0.5)

as:

=(C4>20000)*(D4>0.5)

In Figure 15, column E shows the results of the latter formula.

Figure 15.
If your operation is
AND
, multiply the various logical tests together.

The result of this calculation is always
0
or
1
. It is
0
when the bonus should

not be paid, and it is
1
when the bonus should be paid. Thus, if all your terms

need to be joined by an
AND
, you can simply multiply the terms by the bonus

calculation:

=(C4>20000)*(D4>0.5)*0.02*C4

Figure 16 shows the results of this calculation.