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.

Search JabSto ::

Custom Search