Working with signed numbers other than 10-bit in Excel

Question

Is there some way that I am missing within Excel to set the true number of bits in a binary number, or are we stuck with the 10-bit DEC2BIN and BIN2DEC? 10-bit is a strange choice.

I would, ideally, want to be able to choose between a true signed 8-bit or a true signed 16-bit world, and to be able to perform operations with those numbers. As it stands now, I can't set 11111111 to be -1 instead of 255. Has anyone else dealt with this?

Thanks in advance.

Answer 1

Excel imposes a 10-character limit on binary, octal, and hexadecimal numbers. For binary, this coincides with a 10-bit limit. This limit makes little sense, and I assume it was an arbitrary decision that each Excel version keeps for backward compatibility.

For 8-bit two's complement:

=LEFT(A1)*-128 + BIN2DEC(MID(A1,2,7))

For 16-bit binary, convert each half of the binary number to hex and concatenate. You can feed that result to HEX2DEC. You can then handle two's complement like we did for 8-bit:

=LEFT(A1)*-32768 + HEX2DEC(BIN2HEX(MID(A1,2,7),2) & BIN2HEX(MID(A1,9,8),2))