Home 
Search 
Today's Posts 
#1




BIN2DEC conversion for large binary numbers
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. 
#2




BIN2DEC conversion for large binary numbers
Hi,
Well I think it's 822 and my calculator confirms that and like you I get 202. Someone will no doubt explain why but in the meantime use this =SUMPRODUCT(MID("0"&A1,ROW(INDIRECT("1:"&LEN("0"&A 1))),1)*2^(LEN("0"&A1)ROW(INDIRECT("1:"&LEN("0"&A1))))) Where your binary number is in A1 Mike "ahmedmidany" wrote: 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. . 
#3




BIN2DEC conversion for large binary numbers
On Tue, 15 Dec 2009 08:19:13 0800 (PST), ahmedmidany
wrote: 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. There is a way to use BIN2DEC with large numbers, but I can't recall it. You could use: =SUMPRODUCT(MID(A1,LEN(A1)+1ROW(INDIRECT("1:"&LEN(A1))),1),(2^(ROW(INDIRECT("1 :"&LEN(A1)))1))) Just be aware that if your value is more than 15 digits, you must enter it as text. ron 
#4




BIN2DEC conversion for large binary numbers
XL Help tells you why the answer is wrong  you are limited to 10 bits
and the msb is the sign bit. Chop the number up into bytes (8 bits) and treat each part separately, remembering to multiply by 256 for the upper byte. Hope this helps. Pete On Dec 15, 5:13*pm, Mike H wrote: Hi, Well I think it's 822 and my calculator confirms that and like you I get 202. Someone will no doubt explain why but in the meantime use this =SUMPRODUCT(MID("0"&A1,ROW(INDIRECT("1:"&LEN("0"&A 1))),1)*2^(LEN("0"&A1)RO*W(INDIRECT("1:"&LEN("0"&A1))))) Where your binary number is in A1 Mike "ahmedmidany" wrote: 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. . Hide quoted text   Show quoted text  
#5




BIN2DEC conversion for large binary numbers
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. 
#6




BIN2DEC conversion for large binary numbers
Googling around it seems that Excel can only handle binary numbers <= 511
"Mike H" wrote: Hi, Well I think it's 822 and my calculator confirms that and like you I get 202. Someone will no doubt explain why but in the meantime use this =SUMPRODUCT(MID("0"&A1,ROW(INDIRECT("1:"&LEN("0"&A 1))),1)*2^(LEN("0"&A1)ROW(INDIRECT("1:"&LEN("0"&A1))))) Where your binary number is in A1 Mike "ahmedmidany" wrote: 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. . 
#7




BIN2DEC conversion for large binary numbers
Pete,
I just checked E2003 and you are correct but there is no such explanation of this limitation in E2007 help reproduced below A number system is a systematic way to represent numbers with symbolic characters and uses a base value to conveniently group numbers in compact form. The most common number system is decimal, which has a base value of 10, and a symbolic character set of 0, 1, 2, 3, 4, 5, 6, 7, 8, and 9. However, there are other number systems, and they can be more efficient to use for a specific purpose. For example, because computers use Boolean logic to perform calculations and operations, they use the binary number system, which has a base value of 2. Microsoft Office Excel has several functions that you can use to convert numbers to and from the following number systems: Mike "Pete_UK" wrote: XL Help tells you why the answer is wrong  you are limited to 10 bits and the msb is the sign bit. Chop the number up into bytes (8 bits) and treat each part separately, remembering to multiply by 256 for the upper byte. Hope this helps. Pete On Dec 15, 5:13 pm, Mike H wrote: Hi, Well I think it's 822 and my calculator confirms that and like you I get 202. Someone will no doubt explain why but in the meantime use this =SUMPRODUCT(MID("0"&A1,ROW(INDIRECT("1:"&LEN("0"&A 1))),1)*2^(LEN("0"&A1)ROÂ*W(INDIRECT("1:"&LEN("0"&A1))))) Where your binary number is in A1 Mike "ahmedmidany" wrote: 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. . Hide quoted text   Show quoted text  . 
#8




BIN2DEC conversion for large binary numbers
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. . 
#9




BIN2DEC conversion for large binary numbers

#10




BIN2DEC conversion for large binary numbers
And I thought XL2007 was meant to be better than XL2003 !! <bg
(An XL2000 user) Pete On Dec 15, 7:14*pm, Mike H wrote: Pete, I just checked E2003 and you are correct but there is no such explanation of this limitation in E2007 help reproduced below A number system is a systematic way to represent numbers with symbolic characters and uses a base value to conveniently group numbers in compact form. The most common number system is decimal, which has a base value of 10, and a symbolic character set of 0, 1, 2, 3, 4, 5, 6, 7, 8, and 9. However, there are other number systems, and they can be more efficient to use for a specific purpose. For example, because computers use Boolean logic to perform calculations and operations, they use the binary number system, which has a base value of 2. Microsoft Office Excel has several functions that you can use to convert numbers to and from the following number systems: Mike "Pete_UK" wrote: XL Help tells you why the answer is wrong  you are limited to 10 bits and the msb is the sign bit. Chop the number up into bytes (8 bits) and treat each part separately, remembering to multiply by 256 for the upper byte. Hope this helps. Pete On Dec 15, 5:13 pm, Mike H wrote: Hi, Well I think it's 822 and my calculator confirms that and like you I get 202. Someone will no doubt explain why but in the meantime use this =SUMPRODUCT(MID("0"&A1,ROW(INDIRECT("1:"&LEN("0"&A 1))),1)*2^(LEN("0"&A1)RO**W(INDIRECT("1:"&LEN("0"&A1))))) Where your binary number is in A1 Mike "ahmedmidany" wrote: 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. . Hide quoted text   Show quoted text  . Hide quoted text   Show quoted text  
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 