Binary NOT in excel

[jackleg]

I've been trying to figure this seemingly simple problem out for two days and finally decided to post. Hope someone can help.

I simply want to input a decimal number and get the Binary NOT of that number back in decimal. I know I can use the DEC2BIN and BIN2DEC functions to convert but I can't seem to find a way to NOT the binary number. Any ideas??? Just to clarify I want to input say 5 and get back 250

5 > 00000101 > 11111010 > 250

 

[barry houdini]

Why is 5 = 00000101 - what determines the number of zeroes at the beginning?

If you're always looking at numbers under 256 then surely the answer is always

=255-A1 where your number is in A1

so in your example 255-5 =250

173 > 10101101 > 01010010 > 82

or 255-173 = 82

 

[jackleg]

The bits in that example were arbitrary, however my application requires numbers up to 65535 (16 bits).

In following your solution, I used 65535-A1. The problem now is that excel doesn't seem to like to display over 9 bits.

 

[barry houdini]

What about

=2^MAX(8,LEN(DEC2BIN(A1)))-1-A1

edit: no I don't think that will work for you either! Perhaps I'm being a bit dense but I still don't see why 5 = 00000101 and not 00101 or 101 for instance because that will make a differnec to the result.

 

[barry houdini]

If you want to see up to 18 bits use

=DEC2BIN((A1/512),9)&DEC2BIN(MOD(A1,512),9)

or does this help....?

Book1
	A	B	C	D	E
1
2	1	0000000000000001	1111111111111110	65534
3	15	0000000000001111	1111111111110000	65520
4	255	0000000011111111	1111111100000000	65280
5	6574	0001100110101110	1110011001010001	58961
6	51423	1100100011011111	0011011100100000	14112
7
Sheet1

 

[jackleg]

That is perfect. Thank you. I didn't realize that I could use the MOD function in that way. one of those things that seems so obvious once someone points it out!

The bit length is important as I have a piece of hardware that outputs 16 bit numbers on two wires (one is for 1's, other is for 0's). If the wires are reversed to the main control board, this formula will give me what the controler will see as the number.

 

[RalphA]

May I also say, "Thank you for an elegant and wonderful solution!"?

 

[joncmaloney]

Just a quick reply perhaps a more elegant solution would be:

BITXOR(BITLSHIFT(1,8)-1,A1)

In this case 8 represents the number of bits that are inverted by the bitwise not.

So for then for a 16 bit number
BITXOR(BITLSHIFT(1,16)-1,A1)

and then for a 32 bit number
BITXOR(BITLSHIFT(1,32)-1,A1)