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. 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.

  3. Did you experiment with And, Or and Xor?

    10 And 9 returns 8

    10 Or 9 returns 11

    10 Xor 9 returns 3

  4. [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

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

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

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

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

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

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

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

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

    Thanks
    doco

  9. 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

    Set rs = CreateObject(“ADOR.RecordSet”)
    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:

    Set rs = CreateObject(“ADOR.RecordSet”)
    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.

Leave a Reply

Your email address will not be published.