I am a printer that is trying to print a medical form that is read by the an OCR. Each page has a unique contract number like 567301 then there is a column that has a series of 24 “bubbles” (like on an SAT test). The bubbles are colored in based on converting the contract number to a binary number. Any ideas on how I could use Excel to print this?
“Oh boy”, I thought, “I finally get to use the DEC2BIN function in the Analysis
ToolpackToolpak.” I got the #NUM! error and subsequently learned that the maximum argument is 511. Five hundred eleven? If I was a teenager IM’ing my buddies, I might text “WTF?”. Is 511 the most anyone would want to convert to binary? I must be missing something.
Whenever I have a problem that needs an Excel solution, I first check The Spreadsheet Page because John’s done almost everything. Then I go to some Excel blog so I stop being embarrassed asking questions that I or someone else has already answered.
No dice at The SS Page, but I found this Analysis Toolpak post by the late Frank Kabal. It’s good to 15 decimal places (an Excel limit), but it looks like cal needs 24. Also, there’s no way to pad the insignificant digits. Hooray, I get to write my own. I’ll use Bitwise And.
Optional lPlaces As Long = 4) As String
Dim i As Long
Dim sBin As String
i = 1
sBin = IIf((Abs(lNumber) And i) = 0, “0”, “1”) & sBin
i = i * 2
Loop Until i > Abs(lNumber)
For i = Len(sBin) + 1 To lPlaces
sBin = “0” & sBin
Do Until Len(sBin) Mod 4 = 0
sBin = “0” & sBin
If lNumber < 0 Then
Dec2Bin2 = TwosComp(sBin)
Dec2Bin2 = sBin
Function TwosComp(sBin As String) As String
Dim i As Long
Dim lNum As Long
Dim sTemp As String
For i = 1 To Len(sBin)
sTemp = sTemp & IIf(Mid(sBin, i, 1) = 0, “1”, “0”)
For i = Len(sTemp) To 1 Step -1
lNum = lNum + ((2 ^ (i – 1)) * Val(Mid$(sTemp, Len(sTemp) – i + 1, 1)))
TwosComp = Dec2Bin2(lNum + 1, Len(sBin))
The first decision I made was that the number of places had to be a multiple of 4. There’s nothing wrong with a six bit binary representation, but things got a little complicated with the two’s notation part. Originally I set the optional argument equal to 4 because it just doesn’t seem right to represent 2 as 10. You’ve got to show at least four significant digits or there’s something wrong with the universe.
Then I ran into a little problem with certain numbers. Eight, for instance. Eight in binary is 1000. The two’s complement representation of -8 is 1000 if you only show four digits, which is wrong. In that situation, can I just add one digit (11000)? Does the user of the output have to know how many bits in order to interpret the output correctly? It seems so to me, but I’m not really an expert on binary. Excel’s DEC2BIN shows 10 significant digits when the argument is negative, but that seems a bit arbitrary to me. At least I know what to expect, though. So I made the output a multiple of four which allows for some predictability, but there are still problems.
=Dec2Bin2(15) = 1111
=Dec2Bin2(-15) = 0001
In this case, I need to add more bits to make the negative meaningful, but I don't have to do it for, say, 2 and -2 (0010 and 1110) or do I? So the question is: How many bits do I show and how do I determine that? Suggestions?