How can I do AND operation on two binary numbers in MS Excel?

Z

Zuke

I want to do AND say to the following:
00101001
AND to
10001001

and should get the following answer:

00001001

What function should I use in MS Excel to get this?
 
G

Gary''s Student

All you need to do is to put the zeros and ones in separate cells. Then the
AND function will work:

=--AND(A1,A2)
 
M

Mike Fogleman

Not sure what operation you want to do with these numbers. Converted to
decimal you have asked that
41 AND 137 = 9
If you go to Tools/Add-ins and check Analysis ToolPak, you will get some
additional math functions that work with Binary, Octal and Hexadecimal
numbers.
Mike F
 
R

Ron Rosenfeld

I want to do AND say to the following:
00101001
AND to
10001001

and should get the following answer:

00001001

What function should I use in MS Excel to get this?

I don't know how to do it with a worksheet function. But you can write a UDF
(user-defined function) in VBA to do that.

<alt-F11> opens the VB Editor.

Ensure your project is highlighted in the Project Explorer window. Then
Insert/Module and paste the code below into the window that opens.

You can use the function by typing:

=BinaryAND(00101001,00001001)

or by placing those numbers into cells, and using the cell references.

The AND function in VBA is supposed to do something similar, but I cannot seem
to get it to work properly at this time.

================================
Option Explicit
Function BinaryAND(n1, n2) As String
Dim i As Long
Dim l As Long
Dim b1 As String, b2 As String
Dim Fmt As String
Dim temp As String

l = Application.WorksheetFunction.Max(Len(n1), Len(n2))

Fmt = Application.WorksheetFunction.Rept("0", l)

b1 = Format(n1, Fmt)
b2 = Format(n2, Fmt)

For i = 1 To l
temp = temp & Mid(b1, i, 1) * Mid(b2, i, 1)
Next i

BinaryAND = temp

End Function
===================================


--ron
 
N

NickHK

Zuke,
Not a complete solution yet, but the ArrayFormula "{=F11+F12}" returns
10102002, so with a couple of SUBSTITUTEs you get the answer.
There probably a better way !

NickHK
 
N

NickHK

Reading this again, am I stupid, or does this work ?
(Although a simple addition suffices)

NickHK
 
D

Dana DeLouis

If you set a vba library reference to "atpvbaen.xls", then the following
returns the string "1001"

Sub Demo()
Dim s1, s2, Answer
s1 = "00101001"
s2 = "10001001"

' Returns 1001
Answer = Dec2Bin(Bin2Dec(s1) And Bin2Dec(s2))
End Sub

HTH
 
D

Dana DeLouis

should get the following answer: 00001001

Oops. For leading zero's, a slight change...

Sub Demo()
Dim s1, s2, Answer
s1 = "00101001"
s2 = "10001001"

' Returns 00001001
Answer = Dec2Bin(Bin2Dec(s1) And Bin2Dec(s2), 8)
End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top