Excel byte bits and reversed bit order bytes

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

Excel byte bits and reversed bit order bytes

Excel byte bits and reversed bit order bytes

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.

Formulas

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.

Reversed: =B2*1+C2*2+D2*4+E2*8+F2*16+G2*32+H2*64+I2*128

Example sheet

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.

Advertisements

One thought on “Excel byte bits and reversed bit order bytes

  1. Jamie Stallwood

    Here’s a function to flip a 32-bit address (in cell B4 for example) and display as hex:

    =DEC2HEX(FLOOR(MOD(B4,2^1),2^0)*(2^31)+FLOOR(MOD(B4,2^2),2^1)*(2^29)+FLOOR(MOD(B4,2^3),2^2)*(2^27)+FLOOR(MOD(B4,2^4),2^3)*(2^25)+FLOOR(MOD(B4,2^5),2^4)*(2^23)+FLOOR(MOD(B4,2^6),2^5)*(2^21)+FLOOR(MOD(B4,2^7),2^6)*(2^19)+FLOOR(MOD(B4,2^8),2^7)*(2^17)+FLOOR(MOD(B4,2^9),2^8)*(2^15)+FLOOR(MOD(B4,2^10),2^9)*(2^13)+FLOOR(MOD(B4,2^11),2^10)*(2^11)+FLOOR(MOD(B4,2^12),2^11)*(2^9)+FLOOR(MOD(B4,2^13),2^12)*(2^7)+FLOOR(MOD(B4,2^14),2^13)*(2^5)+FLOOR(MOD(B4,2^15),2^14)*(2^3)+FLOOR(MOD(B4,2^16),2^15)*(2^1)+FLOOR(MOD(B4,2^17),2^16)/(2^1)+FLOOR(MOD(B4,2^18),2^17)/(2^3)+FLOOR(MOD(B4,2^19),2^18)/(2^5)+FLOOR(MOD(B4,2^20),2^19)/(2^7)+FLOOR(MOD(B4,2^21),2^20)/(2^9)+FLOOR(MOD(B4,2^22),2^21)/(2^11)+FLOOR(MOD(B4,2^23),2^22)/(2^13)+FLOOR(MOD(B4,2^24),2^23)/(2^15)+FLOOR(MOD(B4,2^25),2^24)/(2^17)+FLOOR(MOD(B4,2^26),2^25)/(2^19)+FLOOR(MOD(B4,2^27),2^26)/(2^21)+FLOOR(MOD(B4,2^28),2^27)/(2^23)+FLOOR(MOD(B4,2^29),2^28)/(2^25)+FLOOR(MOD(B4,2^30),2^29)/(2^27)+FLOOR(MOD(B4,2^31),2^30)/(2^29)+FLOOR(MOD(B4,2^32),2^31)/(2^31),8)

    Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s