1

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()?

2
  • 1
    Excel has AND(), OR(), and NOT() functions, are these useful to you? – Tom Carpenter Nov 13 '15 at 2:08
  • No. If I replace the inside of the IF with OR(AND(A:A,B:B),C:C) abbreviating the arguments, then it will not work. I am looking for a symbol representing NOT(), if it exists, that I can use in something like the above expression. – domoremath Nov 13 '15 at 3:00
3

* 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 (10), 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).
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.