1

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 conditional 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")
Improve this question
4
  • The number of bits in A and B are the same, but not fixed. Will range from 1 to ~30, depending on the spreadsheet (the sheet is generated on the server and the number of bits varies from one export to another). Commented Jul 16, 2010 at 17:35
  • I'm just curious as to why no VBA here. Is it just a speed thing? You can certainly write a VBA function and call it from a conditional format rule. Commented Jul 16, 2010 at 18:28
  • @jtolle: My Excel files are server-generated by a custom XML Spreadsheet library or binary via NPOI, and thus can't contain macros. My users have an add-in installed with utility functions, but if I attempt to call an add-in function from a conditional formatting rule, I get the error "You may not use references to other worksheets or workbooks for Conditional Formatting criteria." However, the formats can refer to a cell that is then dependent on the add-in function that creates the bit arrays for A and B. Commented Jul 16, 2010 at 19:25
  • I like the summary. If the dec2bin and bin2dec are available by default, which I think is the case for newer versions, we would have true bitwise operators. Commented Jul 16, 2010 at 20:17

2 Answers 2

Reset to default
2

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

Elegant. Love it. DEC2BIN() is only available with the Analysis ToolPak add-in, but the first example will do the trick. Edited to add some detail for actually implementing Boolean operators.
Follow-up question: I would expect FIND() to be faster than SEARCH() since FIND() is case-sensitive and has no wildcard support. Any particular reason for SEARCH()? Also, FIND/SEARCH will implicitly convert the sum of A and B to a string, so no need for TEXT().
I have a basic ms office installed on a mac - nothing special. maybe newer versions come with dec2bin? as for find/search, sounds like you could be right. But in excel, when performance comes into play, run away!
One issue found-- since the number is being stored in decimal, only up to 15 digits are supported. After that Excel loses significant digits trying to add the numbers.
try to mix in 3 bits per digit then ;-)
|
0

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).

Improve this answer

1 Comment

2 & 1 == 0 vs. (2 + 1) > 0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.