Prev: How do I prevent duplicate entry of a sku in a data table?
Next: If or conditional formatting?
From: ahmedmidany on 15 Dec 2009 11:19
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 12:13
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)-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.
> .
>
From: Ron Rosenfeld on 15 Dec 2009 12:25
On Tue, 15 Dec 2009 08:19:13 -0800 (PST), ahmedmidany <ahmedmidany(a)gmail.com>
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
From: Pete_UK on 15 Dec 2009 12:27
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 12:31
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"&A1))),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.
> > .
> >
 |  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?