bitwise functions

[vbano]

I need bitwise functions for a spreadsheet (shift, OR, XOR, etc). Is there a
way to do this in Excel for example?

 

[Mike H]

Excel has an OR function and VBA has both OR and XOR but the latter is not
included as a worksheet function but for XOR you can use.

=NOT(A1)+NOT(B1)=1

another XOR

=(A1<>0)+(B1<>0)=1

I have no idea what 'shift' or etc mean in this context
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Joe User]

Excel has an OR function and [...] for XOR you can use.
=NOT(A1)+NOT(B1)=1

Excel functions are boolean operations, not bitwise operations. Compare the
difference between:

=--AND(12,10)

=bitAND(12,10)

Function bitAND(a as long, b as long) as long
bitAND = a AND b
End Function

Presumably, the OP wants the bitAND result.

For Mike's edification, the difference is: a bitwise function operates on
the individual truth value (0 or 1) of individual bits, whereas a boolean
function operates on the truth value (zero or non-zero) of the entire value.

binAND(12,10) is 8 because 12 is the binary 1100, 10 is the binary 1010, and
8 is the binary 1000.

I have no idea what 'shift' or etc mean in this context

Ostensibly, shift left and right can be accomplish by multiplying and
dividing by 2, assuming the OP does not want circular shifts.

However, that will not shift bits into and out the sign bit. I believe that
handling that will also cover the boundary condition the can result in an
overflow error when "shifting left".


----- original message -----

 

[Joe User]

Ostensibly, shift left and right can be accomplish by multiplying and
dividing by 2, assuming the OP does not want circular shifts.

However, that will not shift bits into and out the sign bit. I believe
that
handling that will also cover the boundary condition the can result in an
overflow error when "shifting left".

I presume that the OP is only interested in bitwise operations for (up to)
32-bit integers -- type Long in VBA. But of course, Excel will store the
result effectively as type Double.

Ostensibly, that is not a problem since type Double has a 52-bit mantissa
and a separate sign bit. But ironically, if the left shift is done in Excel
(multiply by 2), there is no overflow problem.

binAND(12,10) is 8 because 12 is the binary 1100, 10 is the binary 1010,
and 8 is the binary 1000.

Of course, the type Double representation looks very different. But that
does not matter because no precision is lost when converting from 32-bit
integer (Long) to Double, then to 32-bit integer again.


----- original message -----

Excel has an OR function and [...] for XOR you can use.
=NOT(A1)+NOT(B1)=1

Excel functions are boolean operations, not bitwise operations. Compare
the
difference between:

=--AND(12,10)

=bitAND(12,10)

Function bitAND(a as long, b as long) as long
bitAND = a AND b
End Function

Presumably, the OP wants the bitAND result.

For Mike's edification, the difference is: a bitwise function operates on
the individual truth value (0 or 1) of individual bits, whereas a boolean
function operates on the truth value (zero or non-zero) of the entire
value.

binAND(12,10) is 8 because 12 is the binary 1100, 10 is the binary 1010,
and
8 is the binary 1000.

I have no idea what 'shift' or etc mean in this context

Ostensibly, shift left and right can be accomplish by multiplying and
dividing by 2, assuming the OP does not want circular shifts.

However, that will not shift bits into and out the sign bit. I believe
that
handling that will also cover the boundary condition the can result in an
overflow error when "shifting left".


----- original message -----

Excel has an OR function and VBA has both OR and XOR but the latter is
not
included as a worksheet function but for XOR you can use.

=NOT(A1)+NOT(B1)=1

another XOR

=(A1<>0)+(B1<>0)=1

I have no idea what 'shift' or etc mean in this context
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.