I am looking for a way to easily construct binary spreadsheets using bitwise operators such as SHIFT, AND, OR and XOR.

I've looked at Gnumeric but can't find any function that'd allow you to set the base to 2 and do easy binary operations without having to retreat to using the built-in function that is clunky as you have can not use the binary representation.


If you want to just do single Boolean values in a cell, you can use =TRUE() and =FALSE (represented by 1 and 0 ordinarily). There are AND, IF (taking a logical first operand). NOT, OR, and XOR. You can make rows of these values and perform row-wise operations, permutations, etc.

If you want to do bitwise operations on single values instead, you should be able to do that. Those work on the binary representation of integers in a particular range. You should be able to display them in hex. I don't know which products support entry in hex or binary as part of cell formatting, but functions like BIN2DEC, BIN2HEX, DEC2BIN, and DEC2HEX should be useful. Play with these to get what you want. (To introduce a binary value, use BIN2DEC, and to show a result as binary, use DEC2BIN. I know it sounds backwards, but you can think of the BIT operations, below, doing a DEC2BIN on the operands and then doing a BIN2DEC of the result -- the effect is as if it does that, internally it is easier. Play around with a spreadsheet that has these functions and confirm how it works for yourself.)

The OpenFormula bit-wise functions are BITAND, BITLSHIFT, BITOR, BITRSHIFT, and BITXOR. These are expected to work in unsigned values of at least 48 bits. To work in n bits, it is valuable to define a constant 2^n-1 that can be used in BITAND to discard any bits that are expected to be shifted out and BITXOR to complement all the bits (there being no BITCOMP or BITNOT). There is no circular shift, but one can be composed. For anything particularly complex, either the use of row-wise operations on cells having single Boolean values may be best. Alternatively, it may be desirable to define macros for complicated bitwise functions involving rotations and permutations.

These might not be uniformly implemented yet. Check the latest versions of LibreOffice Calc, Apache OpenOffice Calc, and Gnumeric.

Not the answer you're looking for? Browse other questions tagged or ask your own question.