Prev: How do I prevent duplicate entry of a sku in a data table?
Next: If or conditional formatting?
From: "David Biddulph" groups [at] on 15 Dec 2009 12:29
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" <ahmedmidany(a)gmail.com> wrote in message
news:54419773-9aa7-42be-bdac-67800c8194db(a)m26g2000yqb.googlegroups.com...
> 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.


From: Mike H on 15 Dec 2009 14:14
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 <Mi...(a)discussions.microsoft.com> 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"&A1))),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 -
>
> .
>
From: Mike H on 15 Dec 2009 14:15
> 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" <ahmedmidany(a)gmail.com> wrote in message
> news:54419773-9aa7-42be-bdac-67800c8194db(a)m26g2000yqb.googlegroups.com...
> > 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.
>
>
> .
>
From: Bernd P on 15 Dec 2009 14:27
Hello,

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

Regards,
Bernd
From: Pete_UK on 15 Dec 2009 14:57
And I thought XL2007 was meant to be better than XL2003 !! <bg>

(An XL2000 user)

Pete

On Dec 15, 7:14 pm, Mike H <Mi...(a)discussions.microsoft.com> 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 <Mi...(a)discussions.microsoft.com> 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"&A1))),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 -

First  |  Prev  |  Next  |  Last
Pages: 1 2 3 4
Prev: How do I prevent duplicate entry of a sku in a data table?
Next: If or conditional formatting?