CODEWITHSUNDEEP
excelexcel-formula
Luk
Luk

Reputation: 1149

Excel: How to convert a decimal number into a 3 Byte binary

Consider the decimal number 10093264. I want to convert this number into a 3 Byte binary number and show the value of each byte as decimal. I.e. I want to rewrite the aforementioned number as:

154 2 208

So, what would be a possible way to achieve this?

I was thinking DEC2BIN to give me the binary number. But here is the problem: This function allows only inputs of up to 8 bits! My math is not the best, so maybe s.o. can help me out: What do I need to divide by what? I realized, for example, that QUOTIENT(10093264;2^16) gives 154, which is my LSB. But I cannot apply the same method to give me the other bytes.

Upvotes: 0

Views: 1886

Answers (2)

Chronocidal
Chronocidal

Reputation: 7951

Use BITAND (Bitwise And), and Integer division or BITRSHIFT (Bitwise Right Shift)

So, the smallest byte (208) will just be =BITAND(10093264,255) - this will give you the last 8 bits:

  100110100000001011010000 : 10093264
& 000000000000000011111111 : 255
= 000000000000000011010000 : 208

For the next smallest byte (2), you can start by either Integer Division (INT(10093264/256)) or BITRSHIFT (BITRSHIFT(10093264, 8)), then use BITAND on that again:

  100110100000001011010000 : 10093264
→ 000000001001101000000010 : 39426
& 000000000000000011111111 : 255
= 000000000000000000000010 : 2

Then, do the same again for the next byte: either BITRSHIFT by 16 (2 bytes) or divide by 65536 (i.e. POWER(256,2))

  100110100000001011010000 : 10093264
→ 000000000000000010011010 : 208
& 000000000000000011111111 : 255
= 000000000000000010011010 : 208

If you want to do it all in 1 cell (and have Office 365, for the TEXTJOIN function) then you can use this Array Formula. It used LOG to work out how many bytes there are, and outputs with spaces:

=TEXTJOIN(" ",FALSE,BITAND(BITRSHIFT(A1,8*(ROW($A$1:INDEX($A:$A,CEILING.MATH(LOG($A1,256))))-1)),255))

(As an Array Formula, you will need to use Ctrl+Shift+Enter when you put it in)

Upvotes: 2

Kevin Chen
Kevin Chen

Reputation: 122

function

=ROUNDDOWN(10093264/2^16,0) & " " & ROUNDDOWN(MOD(10093264,2^16)/2^8,0) & " " & ROUNDDOWN(MOD(MOD(10093264,2^16),2^8),0)

Upvotes: 1

Related Questions