+ Reply to Thread
Results 1 to 5 of 5

decimal to binary conversion

  1. #1
    tam
    Guest

    decimal to binary conversion

    How do I convert a decimal number >511 to binary?

  2. #2
    David Billigmeier
    Guest

    RE: decimal to binary conversion

    Excel can't do it unless you implement a user defined function to calculate
    it. You can use the windows calculator to convert, it can handle decimal
    numbers up to 18,446,744,073,709,551,615

    --
    Regards,
    Dave


    "tam" wrote:

    > How do I convert a decimal number >511 to binary?


  3. #3
    Dave Peterson
    Guest

    Re: decimal to binary conversion

    You could break up the value into pieces:

    =TEXT(DEC2BIN(INT(A1/512)),REPT("0",9))&TEXT(DEC2BIN(MOD(A1,512)),REPT("0",9))

    This'll work until =512^2-1 (or 262,143).

    Then you'd have to break it up more.

    tam wrote:
    >
    > How do I convert a decimal number >511 to binary?


    --

    Dave Peterson

  4. #4
    Ron Rosenfeld
    Guest

    Re: decimal to binary conversion

    On Thu, 29 Sep 2005 06:23:05 -0700, tam <tam@discussions.microsoft.com> wrote:

    >How do I convert a decimal number >511 to binary?


    You can use this little beauty from Harlan Grove:

    ========================

    Pick your number apart into powers of 512. For numbers from 0 to -1+2^36,

    =DEC2BIN(INT(x/2^27),9)&DEC2BIN(INT(MOD(x,2^27)/2^18),9)
    &DEC2BIN(INT(MOD(x,2^18)/2^9),9)&DEC2BIN(MOD(x,2^9),9)

    ==============================



    Or you can use a UDF. Here is a general function to convert any base to any
    other base in the range stated in the UDF; and also handle decimal places:

    ================================
    Function BaseConvert(Num, FromBase As Integer, _
    ToBase As Integer, Optional DecPlace As Long) _
    As String

    'by Ron Rosenfeld
    'Handles from base 2 to base 62 by differentiating small and capital letters

    Dim LDI As Integer 'Leading Digit Index
    Dim i As Integer, j As Integer
    Dim Temp, Temp2
    Dim Digits()
    Dim r
    Dim DecSep As String

    DecSep = Application.International(xlDecimalSeparator)

    On Error GoTo HANDLER

    If FromBase > 62 Or ToBase > 62 _
    Or FromBase < 2 Or ToBase < 2 Then
    BaseConvert = "Base out of range"
    Exit Function
    End If

    If InStr(1, Num, "E") And FromBase = 10 Then
    Num = CDec(Num)
    End If

    'Convert to Base 10
    LDI = InStr(1, Num, DecSep) - 2
    If LDI = -2 Then LDI = Len(Num) - 1

    j = LDI

    Temp = Replace(Num, DecSep, "")
    For i = 1 To Len(Temp)
    Temp2 = Mid(Temp, i, 1)
    Select Case Temp2
    Case "A" To "Z"
    Temp2 = Asc(Temp2) - 55
    Case "a" To "z"
    Temp2 = Asc(Temp2) - 61
    End Select
    If Temp2 >= FromBase Then
    BaseConvert = "Invalid Digit"
    Exit Function
    End If
    r = CDec(r + Temp2 * FromBase ^ j)
    j = j - 1
    Next i

    If r <> 0 Then LDI = Fix(CDec(Log(r) / Log(ToBase)))
    If r < 1 Then LDI = 0

    ReDim Digits(LDI)

    For i = UBound(Digits) To 0 Step -1
    Digits(i) = Format(Fix(r / ToBase ^ i))
    r = CDbl(r - Digits(i) * ToBase ^ i)
    Select Case Digits(i)
    Case 10 To 35
    Digits(i) = Chr(Digits(i) + 55)
    Case 36 To 62
    Digits(i) = Chr(Digits(i) + 61)
    End Select
    Next i

    Temp = StrReverse(Join(Digits, "")) 'Integer portion
    ReDim Digits(DecPlace)

    If r <> 0 Then
    Digits(0) = DecSep
    For i = 1 To UBound(Digits)
    Digits(i) = Format(Fix(r / ToBase ^ -i))
    r = CDec(r - Digits(i) * ToBase ^ -i)
    Select Case Digits(i)
    Case 10 To 35
    Digits(i) = Chr(Digits(i) + 55)
    Case 36 To 62
    Digits(i) = Chr(Digits(i) + 61)
    End Select
    Next i
    End If

    BaseConvert = Temp & Join(Digits, "")

    Exit Function
    HANDLER: MsgBox ("Error: " & Err.Number & " " & Err.Description & vbLf & _
    "Number being converted: " & Num)

    End Function
    ======================


    --ron

  5. #5
    Bernd Plumhoff
    Guest

    RE: decimal to binary conversion

    Hello,

    I suggest to use my function longdec2bin() which is able to convert almost
    arbitrary big numbers and fractions.

    See http://www.sulprobil.com/html/longdec2bin__.html

    Example: 2005.75 would be converted to 11111010101.11

    HTH,
    Bernd

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1