Binary math

[cecil]

In Excel (Not VBA) is there a way to tell if a bit is set.

For example 10111 AND (bitwise and) 100 = 100, thus bit 2 (counting from the right to left, 0,1,2) is set.
10011 and 100 = 0, thus bit 2 is not set.

Thanks.

[HansV]

Let's say that A1 contains a binary number such as 10111, and that B1 contains a bit number. The following formula returns the bit in A1 in position B1 (starting to count at 0 from the right):

=IF(LEN(A1)<=B1,0,MID(A1,LEN(A1)-B1,1))

If A1 contains a decimal number instead of a binary number (such as 23), use this formula instead:

=IF(LEN(DEC2BIN(A1))<=B1,0,MID(DEC2BIN(A1),LEN(DEC2BIN(A1))-B1,1))

The DEC2BIN function is available by default in Excel 2007 and later; for Excel 2003 or earlier you need to turn on the Analysis ToolPak add-in in Tools | Add-Ins...

[cecil]

Your quick. I was going to head that route, but the techie in me said Excel is math centric and this is a math problem, use math to solve it. Manipulating a string works too. It just don't feel right.

Thanks.

[HansV]

Here is a more mathematical approach. With a decimal number such as 23 in A1, and a bit number in B1, the following formula returns the bit in A1 in position B1 (starting to count at 0 from the right):

=MOD(INT(A1/2^B1),2)

It would be relatively easy to write a set of user-defined VBA functionsto perform binary mathematics, since VBA has binary operators And, Or and XOr. But you mentioned that you wanted to avoid VBA.