This is probably overkill, but if you are willing to use a UDF (user defined function) created in VBA code, then here is a function (with two supporting functions that it needs to make use of) that will handle up to 95 bit addition or subtraction. The function is named BinAddSub. Note that the binary numbers on the worksheet should be placed in cell formatted as Text. To use the function just pass it the main binary number and the binary number you want to add or subtract from it. If you are adding, you do not have to specify the optional third argument because addition is the default action (you can supply a quoted plus sign for the third argument if you wish); however if you are doing a subtraction, you must tell the function that by providing a quoted minus sign as the third argument. You should format the cell you put the formula using this UDF in as Text to preserve leading zeroes. Also, the function returns as many bits as there are in the first argument. Here is the code...
Function BinAddSub(FirstBin As String, SecondBin As String, Optional AddSub As String = "+") As String
If AddSub = "+" Then
BinAddSub = Format(DecToBin(BinToDec(FirstBin) + BinToDec(SecondBin)), String(Len(FirstBin), "0"))
Else
BinAddSub = Format(DecToBin(BinToDec(FirstBin) - BinToDec(SecondBin)), String(Len(FirstBin), "0"))
End If
End Function
' The DecimalIn argument is limited to 79228162514264337593543950266
' (approximately 96-bits) - large numerical values must be entered
' as a String value to prevent conversion to scientific notation.
Function DecToBin(ByVal DecimalIn As Variant, _
Optional NumberOfBits As Variant) As String
DecToBin = ""
DecimalIn = CDec(DecimalIn)
Do While DecimalIn <> 0
DecToBin = Trim$(Str$(DecimalIn - 2 * Int(DecimalIn / 2))) & DecToBin
DecimalIn = Int(DecimalIn / 2)
Loop
If Not IsMissing(NumberOfBits) Then
If Len(DecToBin) > NumberOfBits Then
DecToBin = "Error - Number too large for bit size"
Else
DecToBin = Right$(String$(NumberOfBits, "0") & DecToBin, NumberOfBits)
End If
End If
End Function
' BinaryString argument can be a maximum of 96 digits (either 0's or 1's)
Function BinToDec(BinaryString As String) As Variant
Dim X As Integer
Const TwoToThe48 As Variant = 281474976710656#
For X = 0 To Len(BinaryString) - 1
If X > 48 Then
BinToDec = CDec(BinToDec) + Val(Mid(BinaryString, Len(BinaryString) - _
X, 1)) * TwoToThe48 * CDec(2 ^ (X - 48))
Else
BinToDec = CDec(BinToDec) + Val(Mid(BinaryString, _
Len(BinaryString) - X, 1)) * CDec(2 ^ X)
End If
Next
If Len(BinToDec) > 10 Then BinToDec = CStr(BinToDec)
End Function
HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste all of the above code into the code window that just opened up. That's it.... you are done. You can now use NameOfTheUDF just like it was a built-in Excel function. For example,
=BinAddSub(A1,A2)
=BinAddSub(A1,A2,"+")
=BinAddSub(A1,A2,"-")
If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.
Bookmarks