I forget now the reason why I came up with this spreadsheet real quick one day. I’m sure it was to help explain something to somebody at work, but I digress. This is a neat little bit of math and a fun spreadsheet to look at. It shows all 255 values available to a single byte, the bits, and then the reversed bit ordering and its corresponding decimal value. I also added some conditional formatting to give it a touch of visual spice.
Creating the spreadsheet
I created this sheet by first populating one column with all 256 values that can fit within a single 8-bit byte. After that, I engineered the formula used to pull out the value for each bit within the byte, each being displayed in a separate column. From there, it was simple math to reconstruct the value in reversed bit order. To show the reversed bits, I just copied and re-ordered the original bit columns, with little other modifications required.
The only complexity in the formula at all is for dividing a number down to its bits. This is pretty basic still and only requires using the FLOOR function to help out with resolving the bits.
We start with the most significant bit, dividing the number by 128 (Bit 8’s value, also 2^7) and then flooring the value to determine if the 128 bit is set. The next bit, bit 7, is then determined by using the same method, but first we have to subtract out the previous bit if it was set. This pattern is continued all the way down to bit 1 (2^0).
Bit8: =FLOOR($A2, 128)/128 Bit7: =FLOOR($A2-($B2*128),64)/64 Bit6: =FLOOR($A2-($B2*128)-($C2*64),32)/32 Bit5: =FLOOR($A2-($B2*128)-($C2*64)-($D2*32),16)/16 Bit4: =FLOOR($A2-($B2*128)-($C2*64)-($D2*32)-($E2*16),8)/8 Bit3: =FLOOR($A2-($B2*128)-($C2*64)-($D2*32)-($E2*16)-($F2*8),4)/4 Bit2: =FLOOR($A2-($B2*128)-($C2*64)-($D2*32)-($E2*16)-($F2*8)-($G2*4),2)/2 Bit1: =FLOOR($A2-($B2*128)-($C2*64)-($D2*32)-($E2*16)-($F2*8)-($G2*4)-($H2*2),1)/1
Building the reversed bit decimal value is as simple as multiplying the bit values (1, 2, 4, 8, 16, 32, 64, and 128) by the bits in the reversed order.
Due to the limitations on wordpress.com, I can’t make the spreadsheet directly available for download here. However, I can embed it in a word document and make that available directly. If you do not have Microsoft Word then you should be able to open this file using WordPad. Of course, if you don’t have Word then I would be surprised to find that you have Excel.