Home 
Search 
Today's Posts 
#11




BIN2DEC conversion for large binary numbers
On Tue, 15 Dec 2009 11:14:01 0800, Mike H
wrote: I just checked E2003 and you are correct but there is no such explanation of this limitation in E2007 help reproduced below That's funny. With Excel 2007, when I look at HELP for BIN2DEC, I see: Number is the binary number you want to convert. Number cannot contain more than 10 characters (10 bits). The most significant bit of number is the sign bit. The remaining 9 bits are magnitude bits. Negative numbers are represented using two'scomplement notation. Seems pretty clear to me. ron 
#12




BIN2DEC conversion for large binary numbers
If so, that sounds like another reason for avoiding Excel 2007. :(
It is, however, covered in the offline help for Excel 2007: http://office.microsoft.com/enus/ex...623071033.aspx  David Biddulph "Mike H" wrote in message ... I guess that you haven't looked at help for the BIN2DEC function? I did and while it is mentioned in e2003 there is no equivalent comment in e2007 help. Mike "David Biddulph" wrote: I guess that you haven't looked at help for the BIN2DEC function? "Number is the binary number you want to convert. Number cannot contain more than 10 characters (10 bits). The most significant bit of number is the sign bit. The remaining 9 bits are magnitude bits. Negative numbers are represented using two'scomplement notation." Perhaps you might want to split your 10 digit string in half and use =BIN2DEC(LEFT(A2,LEN(A2)5))*2^5+BIN2DEC(RIGHT(A2,5))  David Biddulph "ahmedmidany" wrote in message ... Hello All, I need your help, i want to convert large binary numbers using excel but whenever i use the BIN2DEC function the result is negative which is not correct. Ex. BIN2DEC(1100110110) the result is 202 but if i use the calculator the result is 822 which is the correct value Any ideas? what shall i do to have the correct value? Thanks in advance A.M. . 
#13




BIN2DEC conversion for large binary numbers
Chaps,
Look at http://sulprobil.com/html/longdec2bin__.html: =longbin2dec(A1,11) = 822 =longbin2dec(A1,10) = 202 It just depends on how long your 2scomplement is... Regards, Bernd 
#14




BIN2DEC conversion for large binary numbers
Ron,
100% definitely not in my E2007 just the intro header I posted in my other post then a description of the syntax for each of the formulae. Excel 2007 (12.0.6514.5000) SP2 MSO (12.0.6425.1000) Mike "Ron Rosenfeld" wrote: On Tue, 15 Dec 2009 11:14:01 0800, Mike H wrote: I just checked E2003 and you are correct but there is no such explanation of this limitation in E2007 help reproduced below That's funny. With Excel 2007, when I look at HELP for BIN2DEC, I see: Number is the binary number you want to convert. Number cannot contain more than 10 characters (10 bits). The most significant bit of number is the sign bit. The remaining 9 bits are magnitude bits. Negative numbers are represented using two'scomplement notation. Seems pretty clear to me. ron . 
#15




BIN2DEC conversion for large binary numbers
Ron,
It now becomes almost surreal the way Excel help works. Convinced that certainly you and probably not myself are mad I checked again. I enter BIN2DEC in excel help and I get a help category Convert numbers to different number systems Not unreasonably (I think) I click this and there is a list of all the conversion formula with the header I posted in my other post i.e. NO mention of the limitation 'feature' of this formula. Also when I enter BIN2DEC I see an option List of worksheet functions (by category) When I click this and then click 'Engineering functions' and navigate to BIN2DEC the formula is described with the limitation. So it seems that in E2007 it depends on where you look for help is a critical factor in getting a precise answer. Well done Microsoft. Mike "Mike H" wrote: Ron, 100% definitely not in my E2007 just the intro header I posted in my other post then a description of the syntax for each of the formulae. Excel 2007 (12.0.6514.5000) SP2 MSO (12.0.6425.1000) Mike "Ron Rosenfeld" wrote: On Tue, 15 Dec 2009 11:14:01 0800, Mike H wrote: I just checked E2003 and you are correct but there is no such explanation of this limitation in E2007 help reproduced below That's funny. With Excel 2007, when I look at HELP for BIN2DEC, I see: Number is the binary number you want to convert. Number cannot contain more than 10 characters (10 bits). The most significant bit of number is the sign bit. The remaining 9 bits are magnitude bits. Negative numbers are represented using two'scomplement notation. Seems pretty clear to me. ron . 
#16




BIN2DEC conversion for large binary numbers
Ah, so it IS better then, Mike !!! <ebg
Pete On Dec 15, 10:36*pm, Mike H wrote: Ron, It now becomes almost surreal the way Excel help works. Convinced that certainly you and probably not myself are mad I checked again. I enter BIN2DEC in excel help and I get a help category Convert numbers to different number systems Not unreasonably (I think) I click this and there is a list of all the conversion formula with the header I posted in my other post i.e. NO mention of the limitation 'feature' of this formula. Also when I enter BIN2DEC I see an option List of worksheet functions (by category) When I click this and then click 'Engineering functions' and navigate to BIN2DEC the formula is described with the limitation. So it seems that in E2007 it depends on where you look for help is a critical factor in getting a precise answer. Well done Microsoft. Mike "Mike H" wrote: Ron, 100% definitely not in my E2007 just the intro header I posted in my other post then a description of the syntax for each of the formulae. Excel 2007 (12.0.6514.5000) SP2 MSO (12.0.6425.1000) Mike "Ron Rosenfeld" wrote: On Tue, 15 Dec 2009 11:14:01 0800, Mike H wrote: I just checked E2003 and you are correct but there is no such explanation of this limitation in E2007 help reproduced below That's funny. *With Excel 2007, when I look at HELP for BIN2DEC, I see: Number * *is the binary number you want to convert. Number cannot contain more than 10 characters (10 bits). The most significant bit of number is the sign bit. The remaining 9 bits are magnitude bits. Negative numbers are represented using two'scomplement notation. Seems pretty clear to me. ron . Hide quoted text   Show quoted text  
#17




BIN2DEC conversion for large binary numbers
Below is a UDF that will handle up to a 96bit binary number (decimal value
79228162514264337593543950335) which I'm guessing is way more than you will ever need.<g The code is efficient (looping only as many times as necessary to process the passed in binary value), so don't worry about it being able to handle such a large binary value. The function returns a real numeric value up to 9999999999 after which it returns text representations of the calculated number. Function BinToDec(BinaryString As String) As Variant Dim X As Integer Const TwoToThe48 As Variant = 281474976710656# For X = 0 To Len(BinaryString)  1 If X 48 Then BinToDec = CDec(BinToDec) + Val(Mid(BinaryString, _ Len(BinaryString)  X, 1)) * _ TwoToThe48 * CDec(2 ^ (X  48)) Else BinToDec = CDec(BinToDec) + Val(Mid(BinaryString, _ Len(BinaryString)  X, 1)) * CDec(2 ^ X) End If Next If Len(BinToDec) 10 Then BinToDec = CStr(BinToDec) End Function  Rick (MVP  Excel) "ahmedmidany" wrote in message ... Hello All, I need your help, i want to convert large binary numbers using excel but whenever i use the BIN2DEC function the result is negative which is not correct. Ex. BIN2DEC(1100110110) the result is 202 but if i use the calculator the result is 822 which is the correct value Any ideas? what shall i do to have the correct value? Thanks in advance A.M. 
#18




BIN2DEC conversion for large binary numbers
Hello,
I moved my webpage: http://sulprobil.com/html/longdec2bin.html And I provided an example file to download. Rick's code is about 20x faster than mine, my code offers negative numbers, fractions and (which you might never need) even larger numbers. Regards, Bernd 
#19




BIN2DEC conversion for large binary numbers
On 12/15/2009 9:28 PM, Rick Rothstein wrote:
Below is a UDF that will handle up to a 96bit binary number (decimal value 79228162514264337593543950335) which I'm guessing is way more than you will ever need.<g The code is efficient (looping only as many times as necessary to process the passed in binary value), so don't worry about it being able to handle such a large binary value. The function returns a real numeric value up to 9999999999 after which it returns text representations of the calculated number. Function BinToDec(BinaryString As String) As Variant Dim X As Integer Const TwoToThe48 As Variant = 281474976710656# For X = 0 To Len(BinaryString)  1 If X 48 Then BinToDec = CDec(BinToDec) + Val(Mid(BinaryString, _ Len(BinaryString)  X, 1)) * _ TwoToThe48 * CDec(2 ^ (X  48)) Else BinToDec = CDec(BinToDec) + Val(Mid(BinaryString, _ Len(BinaryString)  X, 1)) * CDec(2 ^ X) End If Next If Len(BinToDec) 10 Then BinToDec = CStr(BinToDec) End Function Hi. Just throwing out another idea. Len(BinaryString) is more of a constant (calculated each loop), and power (ie 2^x) is sometimes considered "slower." This has no error checking. Function Bin2Dec(str As String) Dim S As String Dim P As Long Dim K As Variant Dim Ans As Variant S = StrReverse(str) K = CDec(1) For P = 1 To Len(S)  1 Ans = Ans + Val(Mid$(S, P, 1)) * K K = K * 2 Next P Bin2Dec = Ans + Val(Mid$(S, P, 1)) * K End Function Sub TestIt() Dim S As String S = WorksheetFunction.Rept("1", 96) Debug.Print Bin2Dec(S) Mid(S, 3, 1) = 0 Debug.Print Bin2Dec(S) Mid(S, 96, 1) = 0 Debug.Print Bin2Dec(S) Mid(S, 95, 1) = 0 Debug.Print Bin2Dec(S) Debug.Print "= = = = = = = =" End Sub Returns: 79228162514264337593543950335 69324642199981295394350956543 69324642199981295394350956542 69324642199981295394350956540 = = = = = = = = Again, just an idea. Dana DeLouis 
#20




BIN2DEC conversion for large binary numbers
On Wednesday, December 16, 2009 6:28:27 AM UTC+4, Rick Rothstein wrote:
Below is a UDF that will handle up to a 96bit binary number (decimal value 79228162514264337593543950335) which I'm guessing is way more than you will ever need.<g The code is efficient (looping only as many times as necessary to process the passed in binary value), so don't worry about it being able to handle such a large binary value. The function returns a real numeric value up to 9999999999 after which it returns text representations of the calculated number. Function BinToDec(BinaryString As String) As Variant Dim X As Integer Const TwoToThe48 As Variant = 281474976710656# For X = 0 To Len(BinaryString)  1 If X 48 Then BinToDec = CDec(BinToDec) + Val(Mid(BinaryString, _ Len(BinaryString)  X, 1)) * _ TwoToThe48 * CDec(2 ^ (X  48)) Else BinToDec = CDec(BinToDec) + Val(Mid(BinaryString, _ Len(BinaryString)  X, 1)) * CDec(2 ^ X) End If Next If Len(BinToDec) 10 Then BinToDec = CStr(BinToDec) End Function  Rick (MVP  Excel) Hi Rick, Great thanks for this. That's wonderfull  i am not a programmer and am trying to solve a logical puzzle and have ended up sitting here and looking for the way to convert 65bit numbers from decimal to binary and back. Your solution is the best of everything that i have managed to find in internet (and have spent 3 days already for this). Could you please be so kind as to make a UDF for converting 96bit decimal number to binary (or 65bit would be enough for me). As this is something that i still struggle to figure out? Anyone else? Please help? Ilya 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
decimal to binary conversion  Excel Worksheet Functions  
Binary Numbers  Excel Discussion (Misc queries)  
decimal to 16 bits binary conversion in Excel?  Excel Worksheet Functions  
Solver returns non binary answer in binary constrained cells  Excel Worksheet Functions  
large binary numbers  Excel Worksheet Functions 