Microsoft Office Tutorials and References

In Depth Information

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

UNDERSTAND BOOLEAN LOGIC: FALSE IS ZERO; AND

IS *,OR IS + AND EVERYTHING ELSE IS TRUE

Challenge:
You want to become a guru at Excel formulas. To master conditional

computing formulas, you need to understand Boolean logic facts.

Background:
For a brief time, I was planning on being an electrical engineer.

While most of the Notre Dame electrical engineering curriculum and I did not

get along, my favorite class was logic design. I learned how to reduce many

decisions down to a series of wires, electricity, and tiny chips that could perform

AND
,
OR
, and
NAND
operations. I learned how to use Karnaugh maps to reduce

a circuit down to the minimal number of chips. It was fascinating stuff, and it

translates amazingly well to writing criteria in Excel.

With integrated circuits, a circuit is either on or off. On means
TRUE
, or
1
.

Off means
FALSE
, or
0
. The table on the left is the truth table for a simple

AND
operation. While one approach is to memorize these rules, you can also

convert a problem to a simple mathematical calculation, as in the table on the

right, which converts
TRUE
to
1
, converts
FALSE
to
0
, and multiplies the two

values. Notice that the results in A and B in the left table are equivalent to the

results in A*B in the right table.

Boolean Values

Digital Values

Condition

A

Condition

B

A and B

Condition A

Condition B

A*B

FALSE

FALSE

FALSE

0

0

0

FALSE

TRUE

FALSE

0

1

0

TRUE

TRUE

TRUE

1

1

1

TRUE

FALSE

FALSE

1

0

0

With integrated circuits, an
OR
gate accepts two or more incoming wires. If any

of the incoming wires are on, the output wire is on. Again, you can memorize

the facts in the table on the left below, or you can simply change the
TRUE
to

1
, the
FALSE
to
0
, and the
OR
sign to a plus sign, as in the table on the right. If

the result is
1
or greater, the entire problem is
TRUE
.