#### What is bit twiddling

#### What is bit twiddling

Sometimes you need to do many logical operations at once. For example, consider the problem where you want to find which numbers exist in both of two strings, which are stored in an 2 cells.

`A1:`

` 12345`

`A2:`

` 246`

The answer is 24, but how would you do it? You could do something with Search but it would be horrendously complicated. Imagine also if the list was not even sorted,

`A1:`

` 32451`

`A2:`

` 426`

Using Search would become even more complex. Using the bit twiddling formulas we will look at here, the formula to solve both problems is the same,

=bitAnd(a1:a2)

You can even sort the list , and remove duplicates, by using:

You can also test if a a list is a subset of another list

bitAnd(a1).

if ( bitand(a1,a2)=a1, “subset”,”not subset”)

if ( bitand(a1,a2)=bitand(a1), “subset”,”not subset”) ‘ this would be a better test, as it would ensure that a1 was sorted in the right order before comparing them.

#### Why would you need it

#### How does it work

- First the list is converted into a string of 1 and 0’s .. a binary mask.. that represents the string of numbers. Note that these lists are treated as a series of individual digits, not treated as a single number. so 246 is 2,4,6 ; not 246. 246 would be represented as a series of bits, 101010, and 12345 as 11111. By the way, read these binary numbers right to left.
- Perform an AND operation on the result. That means that if either corresponding bit is 0, the result will be 0. Only if both are 1 will the result be 1. So 101010 AND 11111 gives us 1010.
- Convert back into a string, 1010 gives 24.

#### User defined functions

There are a few UDFs associated with this capability, but for the moment we will focus on the AND function.

bitAND(a[,b])

- In each case either or both arguments can be a range, a string, or an array.. The result will be a single result or an array, depending on whether argument B is present.
- If only argument A is present, each of the items represented by the range or array A, are ANDed together, and there will be a single result.
- If both A and B are present, and they have the same number items (for example bitAND(A1:A3,B1:B3) ), then each item in A will be ANDed with the corresponding item in B, and the result will be an Array (see array formulas), or a single result if there is only 1 item in each of A and B.
- If both A and B are present, and one of them has only 1 item, the that item will be ANDed with each of the items in the other argument. For example bitAND( C1,A1:A3)., The result will be an array, or a single result if there is only 1 item in each of A and B.

=bitAnd(A65) ‘ Sorts and deduplicates the contents of the list in A65

=bitAND(A20,A21) ‘ ANDS the list in a20 with the one in A21

{=bitAND(“12”,A2:A4)} ‘ entered as an array formula, will and 12 with each of a2:a4 and return an array with 3 results

{=afsep(“”,IF(bitAnd(C64:C72,E66)=C64:C72,B64:B72,””))} ‘ if any of c64:C72 is a subset of C64, show the corresponding value in B64:B72, and use the afsep function to display the resulting array.

#### Example Usage

Subpages