How do I use Excel to convert from decimal to two's complement notation?

Question

How do I use Excel to convert from decimal to two's complement notation?

Answer 1

Excel has a limited Binary capability. There is no direct conversion for 2's compliment, there is not even an invert or exclusive or (XOR) function...

Realize that two's compliment is used to perform subtraction of a binary number by using addition of a two's compliment number....

So do the following in Excel:

The A column will be used for decimals and the B column for Binary

in cell A1 enter 255 in cell B1 enter the formula =DEC2BIN(A1)

Copy cells A1 & B1 into the next three rows


Realize that to invert bits in an 8 bit word you would perform an XOR of your number 180(b10110100)
with 255 (b11111111)

Since Excel doesn't have an XOR you can do a subtraction to get the same bit result an XOR would have provided.

255 - 180 = 75
cell A1 = 255
Cell A2 = your 0 - 255 Number (180)
and in binary

b11111111
b10110100
-------------
b01001011 note leading zero as place holder
This answer is in cell A3 using formula = A1-A2

You have just performed a bit inversion... which is the first step in 2's compliment

Now add 1 to the above binary (or decimal value)

75 + 1 = 76(Cell A4 = A3+1)
b01001011
b00000001
---------------
b01001100 This is your 2's compliment

The math is done in column A and the decimal conversion done in column B using the DEC2BIN function

Answer 2

Twos Complement Converter

Answer 3

DEC2BIN
Converts a decimal number to binary.

Syntax

DEC2BIN(number,places)

Number is the decimal integer you want to convert. If number is negative, valid place values are ignored and DEC2BIN returns a 10-character (10-bit) binary number in which the most significant bit is the sign bit. The remaining 9 bits are magnitude bits. Negative numbers are represented using two's-complement notation.

Answer 4

much easier/quicker, to just do =RIGHT(DEC2HEX(-A1), 4) for 16 bits, or =RIGHT(DEC2HEX(-A1), 8) for 32 bits.

Answer 5

=DEC2BIN((BIN2DEC(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(I3,"1","@"),"0","1"),"@","0"))+1),10)