MS Excel DEC2BIN 32 bit, Rotate No Carry, Sub Table, XOR and Num 0's

I use Microsoft Excel for just about everything all the time. Below is a list of some of the more offbeat things that I have done with it and I find useful. Some of these things I have included in other blog posts where I was writing about something else, and I found that from time to time I would go back to my own blog and look for them to copy into a spreadsheet that I was working on. It's so much easier to copy something than it is to figure it out again... Even though I wrote the blog post, and I don't have too many posts, it was a pain to find the things I was looking for. I decided to not only put them in one place for my own reference I figured I might as well explain more or less how they work in case anyone else was interested. Here you go:

 

  

Decimal To Binary Conversion With Lots of Bits in Excel: DEC2BIN

 

Up to 10 Bit Decimal To Binary Conversion

=DEC2BIN(G22,10)

Yeah big deal!! This is just the built in function in Excel that we all hate because it only converts up to 10 bit numbers. Anything bigger than that and you get that super fun " #NUM! " error... but wait it's worse than that! They actually only allow negative numbers to 512 and positive ones to 511 with the most significant bit being the +/- sign so you don't even get all 10 bits really... That sucks because I always find myself needing more bits than that. Below are some uses of the same DEC2BIN function to get more bits:

 

16 bit Decimal To Binary Conversion (a hard to read version)

=DEC2BIN(INT(G22/256),8)&DEC2BIN(MOD(G22,256),8)

The above line will give you a 16 bit number with all the bits "stuck together" of whatever 16 decimal value is in cell G22 like this:

1011010110011101

  

As you can see all the bits are 'stuck together'. That can be very hard to read especially when you are interested in one bit in particular - like the 9'th one from the right - and you have to count them to find it. To fix that you can change the above line to add a decimal point, a space or anything else between each group of 8 bits (each byte) in the &" "& below:

  

16 bit Decimal To Binary Conversion (easier to read) 

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

and the above number becomes this:

10110101 10011101

  

With the space in there (or anything else...) it's so much easier to read. The below examples have a decimal point in between the bytes but you can put in anything you like between the quotes &" "& and it will be between the bytes.

 

How does this work?? Good question! It works by doing some math to break the "big number" (bigger than 8 bits) down into 8 bit values then converting them individually and then sticking them together with the & (ampersand). That is why in the above example the first part to the left of the & is INT(G23/256) to give the upper 8 bits and the lower 8 bits being derived by the MOD(G23,256) part. If you want to see each of the 8 bits of the 16 bit value in different cells on the spreadsheet just take everything to the left of the ampersand and put it in one cell and everything to the right in another cell.

To get more bits converted its a simple matter of sticking more math conversions together with more &'s like the examples below:

 

24 Bit Decimal To Binary Conversion

=DEC2BIN(INT(G24/65536),8)&"."&DEC2BIN(MOD(G24/256,256),8)&"."&DEC2BIN(MOD(G24,256),8)
   

If you look close at the above 24 bit conversion you can see the last 2 parts separated by the &" "& is very similar to the 16 bit conversion above except a MOD has been switched into the location where the INT was. In addition the upper 8 bits of the 24 bit value is being calculated with the INT(G24/65536) part.

 

32 Bit Decimal To Binary Conversion

=DEC2BIN(MOD(G24/16777216,256),8)&"."&DEC2BIN(MOD(G24/65536,256),8)&"."&DEC2BIN(MOD(G24/256,256),8)&"."&DEC2BIN(MOD(G24,256),8)

The 32 bit example above HAS BEEN FIXED on July 13, 2010 and  is a continuation of the same thing and by now it should be obvious how it works.

 

 

Rotate No Carry: (bit shift with a rotated bit carry)

   

Rotate No Carry is the geeky way to say I want to shift all the bits of my number one direction and if the bits 'fall off' the end just rotate them around to the other side. HERE is a Wikipedia entry about it if you want to know more about it.
  

Math is cool for so many reasons but one really cool reason is if you multiply a number by 2 mod 2^n-1 what you are really doing is a Rotate No Carry to the left one place (almost), where n is the number of bits in your number. If you multiply by 4 mod 2^n-1 you are rotating two places, multiply by 8 mod 2^n-1 is three places, 16, 32, 64 and 128 is obviously rotating more places to the left etc... 

   

So lets say you have an 8 bit number like 01100000 and you multiply it by 8 mod 255 (which is 8 mod 2^n-1), you get 00000011. So in other words the two 1's in the first number above have rotated  three places to the left and back around to the right. I said this math trick almost works because there is one case that it doesn't work and for an 8 bit number that is when the number you are rotating is 255. 
The problem is if you multiply 255 by anything mod 255 you get 0. That sucks because 255 is 11111111 (all one's) so if you multiply 255 by any amount you get all 0' and not all 1's. To get around this little inconvenience I use the IF statement in Excel and say "if the number to rotate is less than 255 then rotate it, otherwise leave it at 255"

So in the 8 Bit Rotate No Carry line below the IF statement is saying "IF(the value in A24 is less then 255) then (multiply A24 by the value in cell C18 mod 255) otherwise (it equals 255)

 

8 Bit Rotate No Carry
  

=IF(A24<255,(MOD(A24*C18,255)),255)
  

This line will Rotate No Carry the 8 bit value in cell A24 to the left the number of bits determined by cell C18.

One important thing to know is what value to use in the C18 position to rotate the bits the desired number of positions. I created a really confusing chart to do that and to also help count from the right or to the left depending on which direction you are trying to rotate.

Confusing Chart:

IN	IN BINARY	IN ROTATED	IN ROTATED	X BY
2	00000010	00000100	4	2
2	00000010	00001000	8	4
2	00000010	00010000	16	8
2	00000010	00100000	32	16
2	00000010	01000000	64	32
2	00000010	10000000	128	64
2	00000010	00000001	1	128
2	00000010	00000010	2	256

  

What this chart is saying is start with the IN number 2 which IN BINARY is 00000010. To rotate it one position to the left X BY 2 (the rotated value is 4 by the way) .  To rotate two positions to the left X BY 4; three positions to the left X BY 8 etc...

Pretty obvious I know but what if you want to rotate it to the right 6 places? It's still pretty obvious but I always find myself scribbling 1's and 0's on scratch paper and counting them. Now I have a chart to look at and so do you!

  

What about rotating values that are more than 8 bits you ask??
This is the exact same thing but instead of using the value of 2^8-1 (255) in the above IF statement for 8 bits you replace it with 2^16-1 (65535) for 16 bits or for 24 bits use 2^24-1 (16777215) and finally for 32 bits use 2^32-1 (4294967295). Below is what that looks like for a Rotate NO Carry of a 24 bit value:

  
24 Bit Rotate No Carry

=IF(G24<16777215,(MOD(G24*4,16777215)),16777215)

   

Again I like confusing charts so I made another one for reference during hot and heavy bit rotation calculations where confusion can be a bit killer. The values in Red above I got out of this chart:

Function	Value	-1
2^8	256	255
2^16	65536	65535
2^24	16777216	16777215
2^32	4294967296	4294967295

  

Here is an exciting example of  the 24 bit DEC2BIN and the Rotate No Carry function!

Say we start with a decimal number like 12632064 in cell G24 which in binary is:
   

11000000 11000000 00000000 (24 Bits)

   

Then we decide to Rotate with No Carry (24 bit) this number left 2 places (multiply by 4):

=IF(G24<16777215,(MOD(G24*4,16777215)),16777215)

   

We get: 196611 which in binary is:
   

00000011 00000000 00000011
   

You can see that it all got rotated two places to the left and the left most 2 bits got rotated around to the right most positions! Thoroughly exciting!!!

  

  

Sub Table OFFSET: (substitute)

The OFFSET function in Excel is handy if you want to substitute one number with another number. OFFSET works just like an array lookup (but the LOOKUP function in excel doesn't, so don't use that one). This is useful if you want to convert numbers from one value to another without any mathematical relationship between the two like a substitution table in a cipher. Here is an example:

 

=(OFFSET(D43,D40,0,1,1))

 

What this will do is count down cells by the value in D40 starting in cell D43 and return whatever value it finds there. For example if the value in cell D40 is 0 then you get the value of D43. If the value in cell D40 is 1 then you get the value of the cell right below D43. If the value in cell D40 is 6 then you get the value in the cell 6 cells below D43and so on...

  

You can also use this to do 2 dimensional array style substitutions and probably a whole lot more. Actually the OFFSET functions does all kinds of neat stuff but I won't go into everything it does because I don't use it for anything else.

  
  

Count Number of 0's: (parity)

  

This is handy when you have a cell with a bunch of different numbers (or characters) in it and you want to know how many of a particular number (or type of character) is in that cell. I use it for counting the number of 1's in a cell that contains a binary number. This is a useful way to find the parity of a binary number or just for counting 1's which is super fun all by itself.

  

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

  

The above line will count the number of 1's (or anything else you put in the blue location) and return how many of them there are. If it's a binary number it will count the number of 1's. You can couple that with something like:

  

=IF(MOD(L12,2)=0,"EVEN","ODD")
  

The above line will tell you if there is an EVEN or an ODD parity or number of 1's (or whatever) in that cell. Endless fun for the entire family!!!

  

   

Exclusive OR XOR:

This one is a MS Excel Visual Basic Function and if you don't know how to write custom functions I suggest you figure it out. It's easy to do by going to Excel and clicking on the Developer Tab (usually far right) and then the Visual Basic tab (usually far left). A new window opens and you can click on that windows HELP button to get the Visual Basic help menu. Also Google "MS Excel Visual Basic" and there is a lot out there to explain it. IMPORTANT: You have to set Excel to a Macro-Enabled worksheet and enable macros in the security properties to get it to work. Once it does work copy the below code into the Visual Basic window and save it. When you go back into excel click on any cell and start typing =EXCLUSIVEOR and at that point the function EXCLUSIVEOR will show up (it didn't before) just like any other built in Excel function.

  

Function EXCLUSIVEOR(Byte1 As Long, Byte2 As Long)
'
' EXCLUSIVEOR (Byte1 , Byte2)
' XOR Exclusive Or of Two 8 bit Byte's
' June 2010 http://ottobelden.blogspot.com
'

EXCLUSIVEOR = Byte1 Xor Byte2
End Function
   

This is really handy if you are doing a lot of bit and byte things. The syntax for it is:

  

=EXCLUSIVEOR(C11,C12)

   

What you will get is the logical XOR (Exclusive Or) of the values in cells C11and C12. You can write your own functions of course and give them different names as long as they are not the same names as built in Excel functions like SUM or OFFSET or anything else. To make functions that do other logical operations change the Xor above to And or Not or something similar! Wow!!

  

You can even write your own Visual Basic code to do pretty much anything as long as it's an "enabled" workbook. For some examples of other code I have written check out these blog posts (scroll to the bottom of each blog post for the example code):

Linear Approximation Table for Cryptanalysis in MS Excel 

More Excel Fun

Those are two of many...

  

That pretty much is the end of everything that I wanted to write today. I may add some more to this post or I might add another post and link to it here if I think of anything else. Like I mentioned at the beginning of this post I wrote this for myself to have a place to put a few of the things I use a lot so I'll have them in one spot.