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!

Monday, November 16, 2009

16 bits with Excel DEC2BIN and other stuff...

Continuing along with the Sawtooth Idea might be a bit insane but I thought that I would anyway. What started out as something to do on the weekend has turned into a statistical quagmire... well not actually I just think that  statistical quagmire sounds neat. Anyway I have messed around with it a bit more and tried a dozen or so different approaches all similar and along the same lines as the origional.


As I mentioned I have tried a bunch of different formulas and silly math for the forward and reverse parts and I have decided that the origional might just be the best. I'm not quite at that decision yet and I'll save all the details for another post but while I am here I thought I'd share a interesting thing about Excel. As so many people have found out the DEC2BIN function in Excel will convert a decimal number into binary but it only works up to 10 bits. That sucks in a way especially for what i was trying to do the other day - convert decimal numbers as big as 16 bits and put the upper 8 bits in one cell and the lower 8 bits in another. But wait there is more!

As if that wasn't enough I wanted the decimal equivalent of the upper 8 bits in one cell and the decimal equivalent of the lower 8 bits in another. I thought about a visual basic for Excel Sub routing to create a custom function in Excel but I opted for just doing it in Excel. Here it is for your enjoyment:
 
=BIN2DEC(DEC2BIN(MOD(BE7/256,256),8))
=BIN2DEC(DEC2BIN(MOD(BE7,256),8))
 
Yes friends that is nuts bit it works! What the above does is it takes the value in cell BE7 and puts the upper 8 bits converted into a decimal number in one cell and the lower 8 bits converted to decimal in the other cell. If you get rid of the BIN2DEC parts you will of course get the bits themselves... like this:
 
=DEC2BIN(MOD(BE7/256,256),8)
=(DEC2BIN(MOD(BE7,256),8)
 
I'm sure that there probably is another way of doing it but that worked for me. You would think that they would give the DEC2BIN conversion more range than 10 bits but for some reason they don't.
 
Those lines came in handy when messing around with the Sawtooth idea and I'll explain more why later but for now that is about all I have to say.

No comments:

Post a Comment