Anding 2 binary numbers

[ceb]

How do I and 2 binary numbers together in a worksheet, without VBA.


cheers Clive

 

[Luke M]

If using 2003, you need to make sure the Analysis ToolPak add-in is installed.


=DEC2BIN(BIN2DEC(A2)+BIN2DEC(A3))

 

[keymaster]

If you mean AND not ADD, then, the problem becomes complicated.


Assuming the numbers are in cells C2 and C3

And, their lengths are not same

and they both are less than 512 (Excel's BIN2DEC & DEC2BIN formulas cannot handle binary numbers with larger than 512 or lesser than -511)


We can calculate the length of the numbers and make then uniform by padding with zeros to left. Assuming such padding is done and new numbers are in E2 & E3

and the length is in D1,


=DEC2BIN(SUMPRODUCT((MID($E$2,($D$1+1-ROW(OFFSET($A$1,,,$D$1))),1)+0),(MID($E$3,($D$1+1-ROW(OFFSET($A$1,,,$D$1))),1)+0),(2^(ROW(OFFSET($A$1,,,$D$1))-1)))&"")


Would give you the AND of both numbers.


Of course, this seems like an awfully lengthy formula to do something simple like ANDing, but I am not sure if there is a better way to do it. So if you know, please share.


Here is a file to understand this better.


http://img.chandoo.org/playground/anding-binary-numbers.xlsx


And more discussion on this is here: http://www.excelhero.com/blog/2010/01/5-and-3-is-1.html


=

 

[ceb]

Thank for you quick response.

I am ANDING not ADDING just to make it clear.


In you anding-binary-numbers.xlsx you can use DEC2BIN(BIN2DEC(C2),$D$1)to do the padding automatically.


I have tried it and it works fine.

Thanks again.


Cheers Clive.