Boolean algebra in Excel

In this video, we’ll look at how boolean algebra is used for AND and OR logic in formulas.

In Boolean algebra, there are only two possible results for a math operation: 1 or 0, which, as we know, correspond to the logical values TRUE and FALSE.

AND logic corresponds to multiplication.

Anything multiplied by 0 is 0, and anything multiplied by 1 remains unchanged.

If I multiply A times B, you can see how this works.

To get a result of 1, both A and B must be 1. All other combinations return zero.

To check this result, I can use the AND function to evaluate A and B.

AND returns either TRUE or FALSE.

To get a 1 or 0, I can use a double negative.

You can see results are now exactly the same.

Now let's look at OR logic. 

OR logic corresponds to addition.

0 + 0 = 0.  0 + 1 and 1 + 0 = 1. Finally, 1 + 1 also equals 1.

This last result may be confusing, but remember that boolean algebra only allows 1s and 0s.

To demonstrate how this works, I'll start with the OR function.

When I evaluate A and B with OR, we get a TRUE or FALSE.

Again, I'll use a double negative to force the result to 1 or 0.

If either A and B are 1, then the result is 1. Only when both A and B are 0 is the result zero.

Now let's generate the same result with addition.

A + B returns 1s and 0s for the first 3 rows, but notice we get 2 in the last row, where A and B are both 1.

This reminds us that although we are simulating Boolean algebra, Excel is not automatically enforcing a boolean result.

We need to handle that ourselves in the formula.

In this case, a simple solution is to check if the result is greater than zero. This will give us a TRUE or FALSE.

And again, I'll use a double negative to get a 1 or 0.

Finally, although we've been working with 1s and 0s, I want to point out that these same formulas produce the same results if we start with TRUE and FALSE values.