decimal to binary conversion

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

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?

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

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

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