#Num Error while converting nos above 512 into BINARY

Dear Forum,

I am getting a #Num Error while converting nos above 512 into BINARY..

CAn someone please suggest an option for the same?

You would probably want to use a UDF ... a single cell native function would be relatively limited in scope given significant digit limitation.

With a UDF you need simply iterate (down) from 2^n to 2^0 and test to see if the iterated value (2^x) is <= remaining value to be apportioned appending the binary string with 1/0 as appropriate.

There are no doubt better examples but in basic terms (ignoring sign)

Please Login or Register  to view this content.

edit: I'm pretty sure shg will have posted something clever previously if you search the board.

How high do you need to go? You can use a workaround like this for 512 to 262143

=DEC2BIN(INT(A2/512))&DEC2BIN(MOD(A2,512),9)

clever swine....

That's one of the first tricks I learnt at another website that dare not mention it's name.....

You can amend to avoid leading zeroes when A2 < 512

=RIGHT(DEC2BIN(INT(A2/512))&DEC2BIN(MOD(A2,512),9),LOG(A2,2)+1)

Sorry for the late response !
Was down with Viral Fever!

Wow all of these options work !

DonkeyOte:

With a UDF you need simply iterate (down) from 2^n to 2^0 and test to see if the iterated value (2^x) is <= remaining value to be apportioned appending the binary string with 1/0 as appropriate.

As mentioned above, I had a small doubt of getting the "n"

Example:

If I have a Cell containing 8 then I know that 2^3 = 8 , however how do i get the answer n=3?I would be happy if you could answer it the same thread..


I got the Solution it is LOG(8,2) which will gve me n=3...

Dear DonkeyOte,

I am sure this works however How Do I use the UDF like I pasted your code In Sheet 1 and then in the Sheet 1 Typed 1010 in cell A1 and tried typing

Please Login or Register  to view this content.

but it did not work can u please advise how do I use it?

Originally Posted by e4excel

How Do I use the UDF like I pasted your code In Sheet 1 and then in the Sheet 1 Typed 1010 in cell A1 and tried typing...

UDFs must be stored in Modules in VBE (Insert -> Module).

Originally Posted by e4excel

If I have a Cell containing 8 then I know that 2^3 = 8 , however how do i get the answer n=3?I would be happy if you could answer it the same thread..

I'm not entirely sure what the question is ... "n" per the UDF is simply the number of digits you want returned in your binary string.
If you want to adjust "n" per the LOG of the value then modify the UDF as appropriate (dll illustrates this with the native function approach)

Here's what I use:

Please Login or Register  to view this content.