Prev: How do I prevent duplicate entry of a sku in a data table?
Next: If or conditional formatting?
From: Ron Rosenfeld on 15 Dec 2009 15:45
On Tue, 15 Dec 2009 11:14:01 -0800, Mike H <MikeH(a)discussions.microsoft.com>
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's-complement notation.

Seems pretty clear to me.
--ron
From: "David Biddulph" groups [at] on 15 Dec 2009 15:58
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/en-us/excel/HP100623071033.aspx
--
David Biddulph

"Mike H" <MikeH(a)discussions.microsoft.com> wrote in message
news:F5153B4F-209B-4BB9-B618-3A4D470BC917(a)microsoft.com...
>> 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 16:38
Chaps,

Look at http://sulprobil.com/html/longdec2bin__.html:
=longbin2dec(A1,11) = 822
=longbin2dec(A1,10) = -202

It just depends on how long your 2s-complement is...

Regards,
Bernd
From: Mike H on 15 Dec 2009 17:04
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 <MikeH(a)discussions.microsoft.com>
> 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's-complement notation.
>
> Seems pretty clear to me.
> --ron
> .
>
From: Mike H on 15 Dec 2009 17:36
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 <MikeH(a)discussions.microsoft.com>
> > 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's-complement notation.
> >
> > Seems pretty clear to me.
> > --ron
> > .
> >
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?