Binary arithmetic in Excel

[Glen Stokes]

I want to set up an IP design spreadhsheet and need to work in binary. How
do I work in something other than decimal. Does Excel handle binary formats
and can it handle logical AND, OR, NOT functions ?

[Laurent Longre]

Excel doesn't handle binary formats, but you can enter binary numbers as
strings and use the functions provided by the Analysis Toolpack (BINDEC,
DECBIN, HEXBIN...) to convert them into decimal or hexadecimal in your
formulae.

It doesn't include bitwise logical operators, too, but these operators
are available in VBA (And, Or, Not, Xor, Eqv and Imp). For instance, '58
And 162' returns 34 (111010 And 10100010 = 100010). You can use these
VBA functions in user-defined worksheet functions, and also combine
these UDFs with the binary <=> decimal conversion functions of the ATP
if you want to handle directly binary numbers:

Function BINAND(N1 As String, N2 As String) As String
BINAND = Dec2bin(Bin2Dec(N1) And Bin2Dec(N2))
End Function

=BINAND("111010","10100010") entered in a cell returns "100010".

If you want to avoid these conversions in the UDFs themselves, you could
also try to build your own logical functions, handling directly binary
"strings". But I don't think that VBA would be a very good choice for
this, because I find that its string manipulation functions are not very
efficient. DLL add-in functions written in C/C++ should be a better
choice.

If I had enough time, I'd like to program a small spreadsheet designed
for binary numbers and calculations only. It would be a great
challenge... :-)

Laurent