Working with signed numbers other than 10-bit in Excel

3
votes

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.

5
excelbinaryexcel-formula
If you want to do operations on the numbers, you will need to work with the "decimal" versions. The binary versions are just strings of "0" and "1" characters. - Tom Robinson
Shame on Excel for this limitation. Probably a compatibilty issue to ancient versions. I struggled over the same thing in LibreOffice which obviously tries to be as compatible as possible - also with such limitations - Daniel Alder
Small clarification. 10 characters and not 10 bit. The number is a 40bit number and not a 8, 16, 32 or 64 standard integer. - Angelo Mascaro
@AngeloMascaro In termw of nomenclature, the data is stored as a string, probably in Unicode. But it is designed to represent bits, and 10-bit is the model I was trying to get away from. Also, I don't understand where the number 40 came from. - Ben I.
@Ben I. You are right: the confusion arises because I searched the function HEX2DEC and my comment was more general than the topic. In BIN2DEC ten chars means ten bits, but in hex ten chars mean 40 bit. The excel limitation of 10 chars is common to all the conversion functions. The maximum length of the HEX string is 10 chars, each two chars are a byte that means 5 bytes = 40 bits If you convert 8000 0000 you don't get a negative number as you can expect. Only hex numbers greater or equal to 80 0000 0000 are converted to negative decimal numbers. - Angelo Mascaro

5 Answers

5
votes

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

enter image description here

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

enter image description here

1
votes

With sign bit+seven data bits in A1

=IF(LEFT(A1,1)="1",BIN2DEC(MID(A1,2,9999))-128,BIN2DEC(MID(A1,2,9999)))

For example:

enter image description here

1
votes

DEC2BIN and BIN2DEC work with 10-bit signed numbers, i.e. -512 thru 511.

However, the binary representation returned by DEC2BIN is a character string.

DEC2BIN(7) returns "111", not 111.

If you try to do math with the resulting binary number, it gets treated like any string used in a numeric context: it is converted from a string to a number using base-10.

DEC2BIN(7+1) returns "1000", but DEC2BIN(7)+1 returns 112. (i.e."111" + 1)

BIN2DEC(99+12) returns 7. (internally there are three steps: 99+12 -> 111 -> "111" -> 7.)

For your needs, I recommend avoiding DEC2BIN and BIN2DEC, and do the conversions using your own VBA functions, such as: http://www.vb-helper.com/howto_decimal_to_binary.html

You could enhance these functions with additional arguments to specify signed/unsigned and 8/16/32-bit, and to return NaN or an error on binary overflow.

The VBA "Long" datatype is 64-bit signed integer (about 18 decimal digits), but you won't be able to take advantage of that going to/from Excel, which handles 15 digits without loss of precision.

VBA "Long" will have no trouble with 32-bit unsigned.

>>>EDIT Correction: VBA Long is 32-bit signed integer.

VBA Double has a 52-bit mantissa, so can be used for at least 48-bit signed integer arithmetic with no loss of precision.

1
votes

just repeat first bit:

for 32 bit to 40 bit

HEX2DEC(
   IF(LEFT(HEX2BIN([@DH6]),1)="1","FF","") 
   &[@DH6]
   &[@DH5]
   &[@DH4]
   &[@DH3]
)
0
votes

That formula doesn't work.

Try the formula with 10000000. The 'Real' answer is 384, but the formula's answer is 768 which is 100000000. I don't know what is wrong with that particular formula?

I found a formula that works:

=SUMPRODUCT(--MID(Z24,LEN(Z24)+1-ROW(INDIRECT("1:"&LEN(Z24))),1),(2^(ROW(INDIRECT("1:"&LEN(Z24)))-1)))