Binary to Decimal

[b.yoxall]

Hi,

I have a 32bit binary number and I need to convert it to a decimal number, the standard BIN2DEC does not work as the number is too large, any assistance?

Ben

[Zer0Cool]

Excel is the wrong tool for dealing with extremely large and/or precise numbers.

Excel can only handle I believe 14/15 digits of significance after which point it truncates beyond those characters. Its also a grey area as to where this happens (final value in a cell definitely, using the results of a calculation mid calc maybe works until the end, etc).

Testing this is simple, type in a blank cell digits (say 20 digits to make a whole number) and hit enter, then look at the number you entered...it will have trailing 0's now.

[Special-K]

Working on this...

[shg]

If the binary strings are always 32 characters (i.e., leading zeros as needed), then

	A	B
2	01111110001111010000011101010001	2117928785
3	11001111111111110101111011001010	3489619658
4	10010100101100000110101100101111	2494589743
5	00110101010000101000011110110010	893552562
6	10100011010110100110000101110000	2740609392
7	11100001100101100010000111011111	3784712671
8	01110100011011101100111101010011	1953419091
9	00101011111000101101010101000010	736286018
10	11111110111101100100000111001001	4277551561
11	01111011000110101111110010101111	2065366191
12	01100010100100110110011010111011	1653827259

The formula in B1 is

=SUMPRODUCT(MID(A2, {1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32}, 1)
* 2^{31,30,29,28,27,26,25,24,23,22,21,20,19,18,17,16,15,14,13,12,11,10,9,8,7,6,5,4,3,2,1,0})

If they vary in length,

	A	B
2	11100100101	1829
3	110100000100110	26662
4	100001101111000010001	1105425
5	111011011001110111010110	15572438
6	1001011110100000010110100	19873972
7	1101111110000	7152

=SUMPRODUCT(MID(RIGHT(REPT(0,31) & A2, 32),
{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32}, 1) *
2^{31,30,29,28,27,26,25,24,23,22,21,20,19,18,17,16,15,14,13,12,11,10,9,8,7,6,5,4,3,2,1,0})

[Special-K]

Here ya go

Input A1 as a string (string can be almost any length)
Converts binary to decimal
All my own work!

=SUMPRODUCT(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)*(2^(LEN(A$1)-ROW(INDIRECT("1:"&LEN(A1))))))

[shg]

The formula I started with was

=SUMPRODUCT(MID(A2, RowVec(1, LEN(A2)), 1) * 2^RowVec(LEN(A2) - 1, 0))

... but that uses a UDF. INDIRECT is compact but volatile.

[Root_]

Here is a non-volatile version:

=SUMPRODUCT(MID(A1,ROW(A$1:INDEX(A:A,LEN(A1))),1)*(2^(LEN(A1)-ROW(A$1:INDEX(A:A,LEN(A1))))))

[daddylonglegs]

Assuming 32 characters you could split the string (within the formula) and use BIN2DEC on each section, e.g. with this formula

=SUM(BIN2DEC(MID(A2,{1,9,17,25},8))*2^{24,16,8,0})