How can I compute the 2s complement of a binary number in Excel. Which Excel functions do I need?
For example, if I have the binary sequence 11101010, the 2s complement can be obtained as,
0001010100010110An excel implementation of the above is what I need.
Remember that Two's complement was invented as a way to to a subtraction in a computer to represent negative numbers, so this problem can be solved as a simple subtraction, so get the complement and then add one.
=DEC2BIN(255 - BIN2DEC(A1) + 1)
Where A1 is a cell with a binary number as text. If you want to use a bigger number then use the biggest number that can be represented by the number of bits that you want to use, here my example is for 8 bits, so (2^8)-1 = 255
Two's complement, in it's basic form is a negative of it's decimal notation.
Much similar to this answer,
If A2 had the binary number, B2:
=RIGHT(DEC2BIN(-BIN2DEC(A2)),LEN(A2))
Caveats:
Scott Craner's answer is great since it implements that the OP had in mind using string manipulation. However, I feel a better idea would be to compute the 2's complement by Subtraction of Decimal numbers.
Use BIN2DEC('Binary String') or simply use the original decimal number that you may have converted to binary at some point.
Then if you want to compute the 8 bit 2's complement of your number you can simply subtract the number from 2^8, using POW(2, 8) - BIN2DEC('Binary String')
Then convert it back to binary using DEC2BIN()
The definition of 2's complement with inverting bit patterns and adding 1 posted by the OP is just a consequence of this more fundamental definition of 2's complement. The author of this tutorial talks about this definition near the bottom of the page.
This formula follows your steps:
=TEXT(DEC2BIN(BIN2DEC(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,1,2),0,1),2,0))+1),REPT("0",LEN(A1)))
; instead of , as your delimiters. Replace all the , with ;.In many cases, you may want the Two's Complement in the same number of bits as the original number.
Building off of Guillermo Adrian Rodrguez Barr's answer, we have a general formula for any number of bits:
=DEC2BIN(2^LEN(A1)-BIN2DEC(A1))
An 8-bit number will result in an 8-bit Two's Complement, and this will be for any number of bits. The +1 is already included because 2^LEN(A1) gets the unsigned bit range + 1.
If you do know exactly how many bits you want and want to use a constant, feel free to replace LEN(A1) with how many bits you want, such as 8. In the op's example, this 8-bit formula would be equivalent to the general formula above:
=DEC2BIN(2^8-BIN2DEC(A1))
