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:

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

not 8 = 1111 1111 1111 0111

-9 = 1111 1111 1111 0111

Some more examples:

-2,148,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.

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

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

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.

Did you experiment with And, Or and Xor?

10 And 9 returns 8

10 Or 9 returns 11

10 Xor 9 returns 3

[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

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

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

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

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

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

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

That sucks!

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

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

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

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

Thanks

doco

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

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.