SOLVED

Do you know how to convert binary numbers to decimal?

Brass Contributor

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

@Steve_SumProductCom 

I suspect DEC2BIN is now largely superseded by

= BASE(targetDec, 2)

= DECIMAL(targetBin, 2)

 

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

@Steve_SumProductCom 

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!

@Steve_SumProductCom 

Alternative with a different formula: =BinaryToDecimal(A1) :smile:

Example file inserted.

@Patrick2788 

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

@Steve_SumProductCom 

I suspect DEC2BIN is now largely superseded by

= BASE(targetDec, 2)

= DECIMAL(targetBin, 2)

 

View solution in original post