# Not Bits

I’ve been thinking about bits and two’s complement lately. Really fun stuff. When I was messing with alternating columns earlier today, I came upon this in the Immediate Window:

?not 2
-3

What the heck? Eventually, I looked up the Not Operator in help and read this

In addition, the Not operator inverts the bit values of any variable and sets the corresponding bit in result according to the following table:…

The table basically says if it’s a zero it goes to one and vice versa. Incidentally, it appears there’s a typo in the table. It looks like the letter ‘o’ instead of the number zero on the first line (Office 2000). All that means is that it’s changing the bits which results in a different number, binarily speaking. Such as ‘not 8’ equals -9. In binary:

8 = 0000 0000 0000 1000
not 8 = 1111 1111 1111 0111
9 = 0000 0000 0000 1001
-9 = 1111 1111 1111 0111

Some more examples:

?not 2,147,483,647
-2,148,483,648
?not 2,147,483,648
overflow error

Does Not convert everything to a Long before it calculates? Is that why this overflows? I’m sure this use of Not is useful somewhere, somehow.

Posted in Uncategorized

## 14 thoughts on “Not Bits”

1. Interesting your use of commas in the examples.
Try using commas after the question mark… It has a tabbing effect.
?not 2,147,483,648

Documentation for Office XP (2002) also has a o instead of 0 for the Not truth table

2. Interesting. I would have expected it to flip all the bits when using a Not.

3. Rob: Hmmm, I copied those numbers from help on Long Data Type, which is why they have commas. I must not have for my actual tests.

Bill: It does flip all the bits. That’s why it adds one before negating.

4. joe billo says:

Did you experiment with And, Or and Xor?

10 And 9 returns 8

10 Or 9 returns 11

10 Xor 9 returns 3

5. doco says:

[quote] I’m sure this use of Not is useful somewhere, somehow.

I had a need to discover ouliers from a list of sales ratios based on a factor of lower quartile and upper quartile. In order to return an array I had to use the following formula.

=AVERAGE(IF(NOT(NOT((RatioList>FactLowerLimit)*(RatioList

6. doco says:

Interesting, my last post was chopped! What was up with that?

=AVERAGE(IF(NOT(NOT((RatioList>FactLowerLimit)*(RatioList

7. doco says:

Chopped again! Hey Dick? What’s going on?

“=AVERAGE(IF(NOT(NOT((RatioList>FactLowerLimit)*(RatioList

8. doco says:

Evidently it does not like the &lt symbol… I’ll try with that

=AVERAGE(IF(NOT(NOT((RatioList&gtFactLowerLimit)*(RatioList&ltFactUpperLimit))),RatioList))

9. doco says:

That sucks!

10. Juan Pablo Gonzalez says:

=IF(A1<B1,IF(C1>D1,E1),F1)

11. Juan Pablo Gonzalez says:

doco, use & l t ;, you missed the ; in your post.

12. doco says:

{=AVERAGE(IF(NOT((RatioList>FactLowerLimit)*(RatioList<FactUpperLimit))),RatioList))}

Did it work? Too bad there wasn’t a preview option.

Thanks
doco

13. doco says:

Damn! I am a clutz. How about an edit option as well! :embarrassed:

14. Jamie Collins says:

I discovered this one quite recently when converting some Jet SQL held in VBA code, the equivalent of

… WHERE some_column = NOT NOW

It took me a while to work out the flawed logic. However, it was still having the desired result because, although in VBA

? NOT NOW
-38707

in Jet SQL

rs.Open “SELECT NOT NOW”, _
“Provider=Microsoft.Jet.OLEDB.4.0;” & _
“Data Source=/Nobook.xls;” & _
“Extended Properties=’Excel 8.0′”
? rs(0)
0

My point? That Jet operators aren’t bitwise; you have to specify the bitwise equivalent e.g. BNOT:

rs.Open “SELECT BNOT NOW”, _
“Provider=Microsoft.Jet.OLEDB.4.0;” & _
“Data Source=/Nobook.xls;” & _
“Extended Properties=’Excel 8.0′”
? rs(0)

Jamie.



Posting code? Use <pre> tags for VBA and <code> tags for inline.