Presenting a number as an indication of a 32 bit string

There is always someone who has done something similar before...

Take my number, convert the bit position into a decimal representation using power function.

Then use the formula provided here to get the value in bit representation:

https://stackoverflow.com/questions/24387894/decimal-to-binary-conversion-for-large-numbers-in-excel

CELL C2 - This gives the bit value

=POWER(2, F2-1)

CELL E2 - This is the bit set in a 32-bit longword

=DEC2BIN(MOD(QUOTIENT(C2,256^3),256),8)&DEC2BIN(MOD(QUOTIENT(C2,256^2),256),8)&DEC2BIN(MOD(QUOTIENT(C2,256^1),256),8)&DEC2BIN(MOD(QUOTIENT(C2,256^0),256),8)

CELL F2 This is the bit position - manually entered

1

CELL G2 - This is the final output which I needed

=CONCATENATE("0b", TEXT(E2, "00000000000000000000000000000000"))

smuggler.ie wrote: »

It must be typo, you mean D2

I had a blank col between C and E. E2 was correct, but essentially the same as what you've said. I should have said CELL E2 instead of CELL D2. Corrected in above post.

Then basically one long, convoluted formula in any desired cell providing you maintain manual data entry in F2 (or mod formula to acomodate)

=CONCATENATE("0b", TEXT(DEC2BIN(MOD(QUOTIENT(POWER(2, F2-1),256^3),256),8)&DEC2BIN(MOD(QUOTIENT(POWER(2, F2-1),256^2),256),8)&DEC2BIN(MOD(QUOTIENT(POWER(2, F2-1),256^1),256),8)&DEC2BIN(MOD(QUOTIENT(POWER(2, F2-1),256^0),256),8), "00000000000000000000000000000000"))

Not that i can determine use of it....

Basically DEC2BIN only works on small values - 511. So, split it down into small chunks and convert them on a piece by piece basis and then finally concatenate them all together at the end.
Without sitting down and fully working through it, it uses QUOTIENT function to find the integer part for each calculation and then uses the MOD function to pass on to next stage.


However, if you have a fresher version of Excel then it is simply:

=BASE(3,2)  # returns 11

=BASE(11,2)  # returns 1011

So, I would just have to use

=BASE(C2, 2)

- I think as I've no way of checking it.

smuggler.ie wrote: »

nested formula i posted is workout from what you provided, just keep your sheet clean, no need for hidden columns


I am under impression that =BASE() is not what you desire.
your(nested) formula returns

11 0b00000000000000000000010000000000 11'th bit from right

were =BASE(F2,2,32)
11 00000000000000000000000000001011 binary of decimal 11


TheChizler line so neat

It would be working off Col C2 - which for 11 would use BASE(1024, 2). But yes, when you know your way around the Excel functions there is always a better solution.

Its part of a command file for testing some hardware. A tool was written which expects the values in a certain format and this has been nailed down to the point where no deviations in types etc will work.
I've not looked into the guts of it as I'm busy enough with this part, but I guess that there is a parser in the tool which expects each line in a specific format.