Bit array AND in Excel?

I have a grid something like this:

  A A A A A
B C C C C C
B C C C C C
B C C C C C
B C C C C C
B C C C C C

Each A and B are numeric values derived from creating a bit array from some other work going on elsewhere in the worksheets.

In C, I need to perform a bitwise AND on the intersecting A and B and test if the result is greater than zero (i.e., there's at least one matching bit value of "1").

This must be a pure Excel formula, can't use macros--it is used in a conditional format. Using macros to simulate conditional formatting is not an option, nor is creating a table that duplicates C and uses a macro to store the answer that the con开发者_Go百科ditional formatting can look at.

The values for A and B could be stored as a string with 1's and 0's if some string magic is easier to perform.

Any ideas?

edit

The accepted answer gives me what I need, but for posterity, here's how to extend it to extend that solution to give bitwise answers back:

AND = SUBSTITUTE(SUBSTITUTE(TEXT(VALUE($A2)+VALUE(B$1),"1","0"),"2","1")
OR = SUBSTITUTE(TEXT(VALUE($A2)+VALUE(B$1),"2","1")
XOR = SUBSTITUTE(TEXT(VALUE($A2)+VALUE(B$1),"2","0")

---

save it as a string of 0 and 1, add them as numbers together, convert that to string and look for a 2.

=ISNUMBER(SEARCH("2",TEXT(VALUE($A2)+VALUE(B$1),"0")))

copy in cell B2 with data in A2 and B1, then copy&paste around.

edit: Wow! they have put in a function DEC2BIN()!

=ISNUMBER(SEARCH("2",TEXT(VALUE(DEC2BIN($A2))+VALUE(DEC2BIN(B$1)),"0")))

and leave them numbers.

---

What about =(A + B) > 0 if you're doing OR (which you're describing) or =(A + B) > 1 if you're doing AND (like you're actually writing).