#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)

Function DEC2BINLARGE(rngVal As Range, Optional lngPlaces As Long = 15)
Dim lngPower As Long, lngRunning As Long, strBin As String
For lngPower = lngPlaces - 1 To 0 Step -1
    If 2 ^ lngPower <= (Int(rngVal) - lngRunning) Then
        strBin = strBin & 1
        lngRunning = lngRunning + (2 ^ lngPower)
    Else
        strBin = strBin & 0
    End If
Next lngPower
DEC2BINLARGE = strBin
End Function

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...