## Links to stuff on this blog

Use the Site Index of Projects page link above for an easier way to find stuff on my blog that you might be looking for!

## Saturday, October 30, 2010

### MS Excel Checking Bit Positions in Bytes and other stuff...

The other day I wanted to make a chart that showed all the numbers up to 255 sorted by the number of bits that are set to 1. Why I wanted to do this I can't remember but I manged to make a chart like that in MS Excel.  I also wanted to make a spreadsheet that would show me all the 1 bit differences from any 8 bit number, all the 2 bit differences, all the 3 bit differences and so on... What I'm writing about is how I went about doing that.

MS Excel pretty much sucks when it comes to dealing with binary numbers. The "=dec2bin()" doesn't work for a useful number of bits and there are no logical operators that you can use directly from Excel. Also there are no parity checking commands or functions that are built in to the spreadsheets. What I wanted to do specifically for my first chart was take a decimal number, convert it to a binary number then count the number of bits that are set to 1 in that number. After I figured out how many 1's there were in the number I wanted to put that number into a list.

For the second chart (or set of charts) that I wanted to make I needed to get a number, do a logical Exclusive Or with another number and then count the number of bits. Anyway here is a neat picture that shows what I came up with to do both of these things. 16 Bit dec2bin() and Parity in Excel

I wrote here about all this stuff before in another post that pretty much just explained how to do this but didn't have any examples of how to use it. This post will have some examples of how it all works.
On the top in the above picture is how the spreadsheet looks (or is supposed to look) and on the bottom the "show formulas" is turned on so you can see what is in the cells. With the exception of the =ExclusiveOr(C5,C4) everything is pretty straight forward. But in case it isn't here is what is happening (I'll explain the ExclusiveOr part below)

The decimal values in cells C4 and C5 are being converted into 16 bit binary values by the formulas in cells D4 and D5. Here is what the formula looks like for converting from decimal to 16 bit binary in Excel.

=DEC2BIN(INT(C4/256),8)&" "&DEC2BIN(MOD(C4,256),8)

What is happening above is the dec2bin() command is being used twice on the same cell C4. The first time it's converting the upper 8 bits of a number and the second time on the lower 8 bits of a number. The results are being 'stuck together' with a space between them by the &" "& in the formula.

The second interesting thing going on is counting the number of bits that are set to 1 after the conversion to binary. This is done in the cells E4 and E5 with the formula below.

=LEN(C5)-LEN(SUBSTITUTE(C5,"1",""))

What is happening here is the length function LEN() is first finding the length of the string in cell C5. In this case the length is 17 characters because there is a space between the first 8 numbers (first byte) and the second 8 numbers (second byte). Like this:

00000000 00100010 = 17 characters

The second thing that is happening is the SUBSTITUTE command is substituting "" which is nothing for all the 1's. After doing the substitution the second LEN() command is finding the length after all the 1's have been substituted with nothing "" or 'substituted away'. Like this:

00000000 00100010 = 17 characters
00000000 000000 = the above values with the 1's substituted with 'nothing'
00000000 000000 = 15 characters
17 - 15 = 2 so two 1's were removed.

What this all amounts to is you have the length before substitution of 17 being subtracted from the length after the 1's have been removed and that leaves you with the difference which is how many 1's have been taken away. Crazy isn't it? Anyway on to the first chart... Bytes sorted by the number of bits set to 1

Anyway the reason that I am explaining all this is because it was important to making the above chart that shows all the bytes in decimal values sorted by the number of bits that are set to 1. Wow!!! Exciting! Near the upper left corner (click on it for a bigger view) of that picture in cells C3, C4 and C5 are what I just explained above. In C4 there is a number in decimal. If cells C4 there is that number converted to binary (16 bits but I only really need 8). And cell C5 is counting the number of 1's in cell C4. That big blue column on the left is the first prime numbers below 255. I added that just for fun to highlight the prime numbers in the chart.

What I did next was write a Visual Basic script that would set the value in cell C3 from 0 to 255. Each time it set the value the script would look at cell C5 to get the number of 1's that cell C3 had after being converted to binary by cell C4. Once the script knew how many 1's there were it copied that value into the chart in the appropriate column. I put the Visual Basic code at the bottom of this post if you are interested. Without wasting any more time below is what all that chart looks like!!!

The numbers in green are the total number of values in each column. The numbers in red are the number of bits set to 1. The numbers below are the values in decimal that have their bits set to 1 in the corresponding column. For example there are 23 values that have 2 bits set to 1, in that column they are 3, 5, 6, 9, (decimal) etc...

 1 8 23 51 65 51 23 8 1 0 1 2 3 4 5 6 7 8 0 1 3 7 15 31 63 127 255 2 5 11 23 47 95 191 4 6 13 27 55 111 223 8 9 14 29 59 119 239 16 10 19 30 61 123 247 32 12 21 39 62 125 251 64 17 22 43 79 126 253 128 18 25 45 87 159 254 20 26 46 91 175 24 28 51 93 183 33 35 53 94 187 34 37 54 103 189 36 38 57 107 190 40 41 58 109 207 48 42 60 110 215 65 44 71 115 219 66 49 75 117 221 68 50 77 118 222 72 52 78 121 231 80 56 83 122 235 96 67 85 124 237 129 69 86 143 238 130 70 89 151 243 132 73 90 155 245 136 74 92 157 246 144 76 99 158 249 160 81 101 167 250 192 82 102 171 252 84 105 173 88 106 174 97 108 179 98 113 181 100 114 182 104 116 185 112 120 186 131 135 188 133 139 199 134 141 203 137 142 205 138 147 206 140 149 211 145 150 213 146 153 214 148 154 217 152 156 218 161 163 220 162 165 227 164 166 229 168 169 230 176 170 233 193 172 234 194 177 236 196 178 241 200 180 242 208 184 244 224 195 248 197 198 201 202 204 209 210 212 216 225 226 228 232 240

Thrilling I know but the fun doesn't stop there!

After doing that I couldn't bring myself to stop it was so much fun and I decided to write another Visual Basic script that would list all the numbers that were different from a 'test number' by a certain number of bits. That last sentence is confusing so let me pit another picture in here. Byte values that are different by 1 bit from all numbers in Excel
What is going on here is similar in some ways to what I explained above.  Let me start by explaining the picture. In column 'E' that says "Test Num" at the top are a bunch of decimal values. To the right in each corresponding row are the decimal values of the numbers that differ from the 'Test Num" by one bit. So for the first 'Test Num' of 0 the values 1, 2, 4, 8, 16 etc... differ from 0 by one bit. Can you believe it?!?! Again just for the hell of it I highlighted all the prime numbers in blue.

To do this I wrote a Visual Basic script that sets the value of cell C4 to 0 through 255. For each of the values in cell C4 the script sets the value in cell C3 from 0 to 255. Then the spreadsheet performs a logical Exclusive Or of those two cells using a custom function I wrote:

Function ExclusiveOr(A As Long, B As Long)
ExclusiveOr = A Xor B
End Function

The results of an Exclusive Or give you 1's everywhere bits are different in a pair of values. The Function above is pretty straight forward creating 2 variables A and B as data types Long. Then the function returns the Exclusive Or of those values with the Visual Basic Xor. Oh Yeah!

Once the Exclusive Or is calculated the script then puts that number in the row next to the 'test num' if the Exclusive Or result is equal to the value in cell C1. By changing that value and re-running the script I can make a chart that shows differences of any number of bits I want. To see the results of a few choice values go to this pre-dated post where I put all the results of this Excel insanity!!

One last thing I should mention about the above picture is the Excel "FALSE" value in cell C6. That is a booboo that I made before I took the screen shot and was fixed some time later... Send me an email at ottobelden@yahoo.com or leave a comment if you have any questions or see something wrong.

MS Excel Visual Basic Code:
The first block of code is what I wrote to sort the bytes by the number of bits that are set to 1.
Sub BitSort_1()
' This code will sort numbers based
'  on how many bits are set to 1
'  in each number then highlight
'  which numbers are prime
'
' http://ottobelden.blogspot.com/
' October 2010
'
Dim PrimeRow As Integer
Dim PrimeCol As Integer
PrimeRow = 2                ' top of prime
PrimeCol = 1
Dim TempCol As Integer
Dim TempRow As Integer
'
' Set Row Counters to 3
For SetRow = 0 To 16
Cells(1, (SetRow + 5)) = 3
Next SetRow
'
For Number = 0 To 255
Cells(3, 3) = Number    ' set num to test
TempCol = Cells(5, 3) + 5 ' get column
TempRow = Cells(1, TempCol) ' get offset
Sheets("1000_Primes_Raw").Select
If Cells(2, 11) = Cells(PrimeRow, PrimeCol) Then
Sheets("65536 Primes").Select ' grab fill color
Cells(1, 1).Copy
Cells(TempRow, TempCol) = Cells(3, 3)
Cells(TempRow, TempCol).PasteSpecial xlFormats
PrimeRow = PrimeRow + 1
Sheets("1000_Primes_Raw").Select
Else
Sheets("1000_Primes_Raw").Select
Cells(TempRow, TempCol) = Cells(3, 3) ' place number
End If
Cells(1, TempCol) = Cells(1, TempCol) + 1 'increment row count
Next Number
End Sub

This is the code that I wrote to generate the lists of bytes that have a specific number of bit differences. Check out the picture and text above for how the sheet needs to be set up for this to work.

Sub BitsDiff()
' This code will sort numbers based
'  on how many bits are different
'  in each number from a test number
'  then highlight which numbers are prime
' The number of bits diff to test for
'  is in cell C1
' The Number to test against is in
'  cell C4
' http://ottobelden.blogspot.com/
' October 2010
'
Dim OutputCol As Integer
OutputCol = 6   'Starting column for output
Dim PrimeYes As Integer
PrimeYes = 0
Dim PrimeChk As Integer
PrimeChk = 2
'
Application.ScreenUpdating = False
For TestRow = 2 To 257
Cells(4, 3) = Cells(TestRow, 5) ' set num to test
For Test = 0 To 255 ' run thru num's to test
Cells(3, 3) = Test
If Cells(PrimeChk, 1) = Test Then
PrimeYes = 1 'The number tested is a prime
PrimeChk = PrimeChkPrimeChk = PrimeChk + 1
End If
If Cells(7, 3) = Cells(1, 3) Then ' right num of bits
If PrimeYes = 1 Then
Cells(1, 1).Copy
Cells(TestRow, OutputCol).PasteSpecial xlFormats
End If
Cells(TestRow, OutputCol) = Test
OutputCol = OutputCol + 1
End If
PrimeYes = 0
Next Test
OutputCol = 6   ' reset output col for next number
PrimeChk = 2    ' reset prime checking to top of col
Next TestRow
Application.ScreenUpdating = True
End Sub

1. Hello,
I am a newbie to Excel VBA. Can anyone help me developed a module or function that will check the number of bits of a binary number and then calculate the resistance of a binary resistors in parallel combination. One column of excel sheet contains binary data to be checked i.e.
0000,0000,0000,0001
0000,0000,0000,0010
0000,0000,0000,0011
0000,0000,0000,0100
;
;
0000,0011,1111,1111
0000,0100,0000,0000

for example.
if binary number is 0000,1111
then the resultant of R0, R1, R2, R3 should be calculated i.e. Rs=1/R0+1/R1+1/R2+1/R3

The values of binary resistors in parallel are:
R0 R1 R2 R3 R4 R5 R6 R7 R8 R9 R10
1 2 4 8 16 32 64 128 256 512 1024

Regards,
Kein

2. Kein,

That is an interesting problem, I'd be happy to help if I can. May I ask what this is for? Is the binary data:
0000,0000,0000,0011
all in one column - separated by commas as you have written or are there no commas?
I believe that the formula for the resistances in parallel would be:
1/Rt = 1/R0+1/R1+1/R2+1/R3...
Let me think about this a bit and if I come up with something I'll post it on my blog. You can always email me too ;-)

- Otto

3. Kay Oddvar RamsdalJune 16, 2012 at 7:00 AM

Hi.

I have been trying to make exactly this, but my skill in excel just isn't good enough :P
Never even tried VBA, and was hoping to do this as simple as possible, with plain excel.
The reason I would like to have this, is to have several different things in one excel workbook. mA to raw-value i now have, and my next step was this...
Is it possible to get this excel workbook from you? I will probably understand it more easily anyway then since VBA is not something I've used, and I'm a novice with excel to begin with :P

1. Kay,

Thanks for checking out my blog! I can't send you the Excel sheet that I did this on :-( I have a lot of data on there and I wouldn't feel comfortable sending it. My suggestion is that you create a new spreadsheet and copy the cove that I have (above) into your sheet. There are a lot of explanations about how to do that on the web.
If you have trouble doing it let me know and I can help.
The Spreadsheet has to have Macro's enabled" and I think one other item checked for the VBA stuff to work.
I too am a novice with Excel and had to figure this stuff out as I went along.
One easy way i found to get started is to create a new spreadsheet then "record macro" and click on some cells, add some number etc... then "stop recording". AT that point you can go and edit your macro (erase the parts you just did) and use it as an example to start with and build from there.
Let me know if you have any problems and I'll do my best to help!

Otto