Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old December 15th 09, 04:19 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Dec 2009
Posts: 1
Default 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   Report Post  
Old December 15th 09, 05:13 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2007
Posts: 11,501
Default 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   Report Post  
Old December 15th 09, 05:25 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 5,651
Default 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)+1-ROW(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   Report Post  
Old December 15th 09, 05:27 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 8,856
Default 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   Report Post  
Old December 15th 09, 05:29 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Feb 2007
Posts: 8,651
Default 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's-complement 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   Report Post  
Old December 15th 09, 05:31 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2007
Posts: 11,501
Default 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   Report Post  
Old December 15th 09, 07:14 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2007
Posts: 11,501
Default 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   Report Post  
Old December 15th 09, 07:15 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2007
Posts: 11,501
Default 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's-complement 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   Report Post  
Old December 15th 09, 07:27 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jun 2007
Posts: 806
Default BIN2DEC conversion for large binary numbers

Hello,

I suggest to use my UDF at:
http://sulprobil.com/html/longdec2bin__.html

Regards,
Bernd
  #10   Report Post  
Old December 15th 09, 07:57 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 8,856
Default 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
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
decimal to binary conversion tam Excel Worksheet Functions 9 November 30th 13 07:30 PM
Binary Numbers Sanford Lefkowitz Excel Discussion (Misc queries) 9 May 12th 10 04:06 PM
decimal to 16 bits binary conversion in Excel? xcgames Excel Worksheet Functions 2 March 27th 06 12:19 AM
Solver returns non binary answer in binary constrained cells Navy Student Excel Worksheet Functions 6 September 1st 05 03:11 PM
large binary numbers Himu Excel Worksheet Functions 4 July 27th 05 02:53 AM


All times are GMT +1. The time now is 12:57 AM.

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017