Home |
Search |
Today's Posts |
#1
|
|||
|
|||
decimal to binary conversion
How do I convert a decimal number 511 to binary?
|
#2
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
decimal to binary conversion
hello can you help fast ? tonight /
How do I convert a decimal number 23.125 to binary? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I convert binary numbers to decimal when they are larger . | Excel Worksheet Functions | |||
Decimal Conversion | Excel Discussion (Misc queries) | |||
How do I override fixed decimal place settings in EXcel 2003? | Excel Worksheet Functions | |||
decimal point override does not work | Excel Discussion (Misc queries) | |||
Decimal to Binary | Excel Worksheet Functions |