Forum Discussion
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)
I suspect DEC2BIN is now largely superseded by
= BASE(targetDec, 2) = DECIMAL(targetBin, 2)
I suspect DEC2BIN is now largely superseded by
= BASE(targetDec, 2) = DECIMAL(targetBin, 2)PeterBartholomew1 - thank you for mentioning those. I had not used them before, but I agree with you.
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!
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.
Alternative with a different formula: =BinaryToDecimal(A1)
Example file inserted.
