Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
tam
 
Posts: n/a
Default decimal to binary conversion

How do I convert a decimal number 511 to binary?
  #2   Report Post  
David Billigmeier
 
Posts: n/a
Default

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   Report Post  
Dave Peterson
 
Posts: n/a
Default

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   Report Post  
Ron Rosenfeld
 
Posts: n/a
Default

On Thu, 29 Sep 2005 06:23:05 -0700, tam 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   Report Post  
Bernd Plumhoff
 
Posts: n/a
Default

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


  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default decimal to binary conversion

hello can you help fast ? tonight /
How do I convert a decimal number 23.125 to binary?

  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,514
Default decimal to binary conversion

hello can you help fast ? tonight /
How do I convert a decimal number 23.125 to binary?


''''''''''''''''''
'by Rick Rothstein
''''''''''''''''''
Function DecToBin(ByVal DecimalIn As Variant, Optional NumberOfBits As
Variant) As String
' The DecimalIn argument is limited to 79228162514264337593543950266
' (approximately 96-bits) - large numerical values must be entered
' as a String value to prevent conversion to scientific notation.

DecToBin = "": DecimalIn = CDec(DecimalIn)
Do While DecimalIn < 0
DecToBin = Trim$(Str$(DecimalIn - 2 * Int(DecimalIn / 2))) &
DecToBin
DecimalIn = Int(DecimalIn / 2)
Loop
If Not IsMissing(NumberOfBits) Then
If Len(DecToBin) NumberOfBits Then
DecToBin = "Error - Number too large for bit size"
Else
DecToBin = Right$(String$(NumberOfBits, "0") & DecToBin,
NumberOfBits)
End If
End If
End Function

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion



---
This email is free from viruses and malware because avast! Antivirus protection is active.
http://www.avast.com

  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,045
Default decimal to binary conversion

On Wed, 27 Nov 2013 15:36:41 -0500, GS wrote:

hello can you help fast ? tonight /
How do I convert a decimal number 23.125 to binary?


''''''''''''''''''
'by Rick Rothstein
''''''''''''''''''
Function DecToBin(ByVal DecimalIn As Variant, Optional NumberOfBits As
Variant) As String


When I try to implement this, it only converts the integer part, not the decimal part.

I usually use the BAseChange function in xNumbers (do a web search for this free add-in), for stuff like that.
  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,514
Default decimal to binary conversion

On Wed, 27 Nov 2013 15:36:41 -0500, GS wrote:

hello can you help fast ? tonight /
How do I convert a decimal number 23.125 to binary?


''''''''''''''''''
'by Rick Rothstein
''''''''''''''''''
Function DecToBin(ByVal DecimalIn As Variant, Optional NumberOfBits
As Variant) As String


When I try to implement this, it only converts the integer part, not
the decimal part.


I seem to recall something about this, now that you mention it. I've
never used/tested it myself, though, to be honest!

I usually use the BAseChange function in xNumbers (do a web search
for this free add-in), for stuff like that.


--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion



---
This email is free from viruses and malware because avast! Antivirus protection is active.
http://www.avast.com

  #10   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 829
Default decimal to binary conversion

wrote:
hello can you help fast ? tonight /
How do I convert a decimal number 23.125 to binary?


What do you mean by "binary"?

Do you mean 10111.001 for 23.125?

If so, what do you want for 24.05?

Ostensibly, it is 11000.000011001100(1100)... (repeating the last 4 bits).
0.05 cannot be represented exactly in binary.

Or do you really mean that you want the 64-bit binary floating-point
representation?

If so, hex representation might be best; for example, &h40380CCC,CCCCCCCD
for 23.05.

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How can I convert binary numbers to decimal when they are larger . Nebulae Excel Worksheet Functions 1 April 14th 05 05:50 PM
Decimal Conversion Brian Excel Discussion (Misc queries) 3 February 21st 05 09:32 PM
How do I override fixed decimal place settings in EXcel 2003? jroyv Excel Worksheet Functions 2 February 11th 05 07:07 PM
decimal point override does not work Sam Brauen Excel Discussion (Misc queries) 0 January 6th 05 06:29 PM
Decimal to Binary Himu Excel Worksheet Functions 15 November 22nd 04 08:13 PM


All times are GMT +1. The time now is 03:56 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"