SOLVED

# Do you know how to convert binary numbers to decimal?

Brass Contributor

# Do you know how to convert binary numbers to decimal?

It's easy with the BIN2DEC function, but there's a limitation and an easy way to make a mistake and get a wrong result.  The limitation is that it can't convert a number with more than 10 bits. If you try, it will give an error. There's also an easy way to make a mistake. If you use numbers that have 10 bits, they may be converted to a negative number. For an explanation, you need to understand the "Two's complement" method of representing binary integers.

I found this article by Excel MVP Frédéric LE GUEN, that provides a workaround to the 10 bit limitation of the BIN2DEC function:

Convert Binary Number to Decimal in Excel :keycap_0: :keycap_1: (excel-tutorial.com)

5 Replies
best response confirmed by Steve_SumProductCom (Brass Contributor)
Solution

# Re: Do you know how to convert binary numbers to decimal?

I suspect DEC2BIN is now largely superseded by

``````= BASE(targetDec, 2)

= DECIMAL(targetBin, 2)``````

# Re: Do you know how to convert binary numbers to decimal?

@Peter Bartholomew - thank you for mentioning those. I had not used them before, but I agree with you.

# Re: Do you know how to convert binary numbers to decimal?

At present I prefer this method:

``=MOD(BITRSHIFT(numbers,SEQUENCE(,10,9,-1)),2)``

I realize it can be done with MOD and simple division but I like the way this reads.  I used this method in a project I'm working on where it needed to generate 1 million + bin elements and it performed well.  It's good to give BITRSHIFT some exercise, too!

# Re: Do you know how to convert binary numbers to decimal?

Alternative with a different formula: =BinaryToDecimal(A1)

Example file inserted.

# Re: Do you know how to convert binary numbers to decimal?

I wouldn't say I prefer the proposed method, but it is more interesting and expresses key concepts with elegance.

I rather regret not using

``````= VSTACK(
BITLSHIFT(combination0, 1) + 1,
BITLSHIFT(combination1, 1)
)``````

to combine two lists of combinations, following Pascal's triangle.  Timing runs showed

``````= VSTACK(
2 * combination0 + 1,
2 * combination1
)    ``````

to be faster.

1 best response

Accepted Solutions
best response confirmed by Steve_SumProductCom (Brass Contributor)
Solution

# Re: Do you know how to convert binary numbers to decimal?

I suspect DEC2BIN is now largely superseded by

``````= BASE(targetDec, 2)

= DECIMAL(targetBin, 2)``````