Not boolean operation in Excel

Question

In Excel if I have some boolean operation of the form

{=SUM(IF((A1:A8="Google")*(B1:B8="Stanford")+(C1:C8="Columbia"),1,0))}

I know that * means AND() and + means OR() so that we are searching for ones in columns A and B being "Google" and "Stanford" respectively or those in column C being "Columbia".

But is there a symbol for NOT()?

Answer 1

* works as AND() and + works as OR() because TRUE has a value of 1 and FALSE has a value of 0.  And, since the NOT() function turns TRUE into FALSE and vice versa (1↔0), a simple way of implementing NOT(x) arithmetically is 1-x.

Unfortunately, that works only for 0 and 1.  If you’re using + for OR(), and you have two (or more) TRUE conditions combined in an expression, you will get a value of 2 or greater.  The good news (which you already know) is that any non-zero number counts as TRUE.  The bad news is that 1-2 is -1, which is non-zero and hence also TRUE.

An alternative solution is the logical expression x=0, e.g.,

(((A1:A8="Google")*(B1:B8="Stanford")+(C1:C8="Columbia"))=0)

If x is 0, this logical expression evaluates as TRUE.  If x is anything other than 0, the expression evaluates as FALSE.  Thus, x=0 functions as NOT(x).

Warning: if you combine multiple Boolean (TRUE or FALSE) values with *, +, and -, they are converted into integers (numbers); but if you do a number=number test, you end up with a Boolean.  Thus, you could say something like

=SUM((A1:A8="Google")*(B1:B8="Stanford"))

(without using IF()), because Boolean*Boolean is a number, but you cannot do

=SUM(((A1:A8="Google")*(B1:B8="Stanford"))=0)

because SUM() does not work on Boolean values.  But you can use it

• in an IF(),
• by adding 0 (+0), or
• by multiplying by 1 (*1).