BIN2DEC conversion for large binary numbers
ahmedmidany
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.
Mike H
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:
> .
>
Ron Rosenfeld
wrote:
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
Pete_UK
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...@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)-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.
> > .- Hide quoted text -
>
> - Show quoted text -
David Biddulph
"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" <ahmed...@gmail.com> wrote in message
news:54419773-9aa7-42be...@m26g2000yqb.googlegroups.com...
Mike H
Mike H
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:
> .
>
Mike H
I did and while it is mentioned in e2003 there is no equivalent comment in
e2007 help.
Mike
"David Biddulph" wrote:
> .
>
Bernd P
I suggest to use my UDF at:
http://sulprobil.com/html/longdec2bin__.html
Regards,
Bernd
Pete_UK
(An XL2000 user)
Pete
Ron Rosenfeld
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
David Biddulph
It is, however, covered in the offline help for Excel 2007:
http://office.microsoft.com/en-us/excel/HP100623071033.aspx
--
David Biddulph
"Mike H" <Mi...@discussions.microsoft.com> wrote in message
news:F5153B4F-209B-4BB9...@microsoft.com...
Bernd P
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
Mike H
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:
> .
>
Mike H
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
Pete_UK
Pete
Ron Rosenfeld
wrote:
>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,
The first sequence I went through was to access help for BIN2DEC using the
function wizard. ie. Type =bin2dec in the function bar; select the Fx; select
HELP on the dialog box. This brings up help for that particular function.
So then I typed bin2dec in Excel help -->
==========================================
Engineering functions (reference)
Help > Function reference > Engineering
Convert numbers to different number systems
Help > Formula and name basics > Examples of formulas > Conversion
List of worksheet functions (alphabetical)
Help > Function reference
List of worksheet functions (by category)
Help > Function reference
=====================================
Selection #2 was the obvious and, in addition to the information you posted
earlier, also had a "What do you want to do?" line.
I select "Convert a binary number to decimal" which takes me down to an area
which gives both an example, and an option to select the details of the BIN2DEC
function. When I select that hyperlink, it takes me to the info on the BIN2DEC
function including its limitations.
To me, it seems like a logical progression.
If you're not seeing that, I would suspect a problem with Internet Explorer. I
have had a problem with display of HELP files; the usual solution has to do
with clearing temporary Internet files, etc. However, I progressed through a
variety of solutions until the next step was to reinstall Windows XP. (I
didn't do that since I was planning to upgrade to Windows 7 anyway, and that
solved the problem).
--ron
Rick Rothstein
79228162514264337593543950335) which I'm guessing is way more than you will
ever need.<g> The code is efficient (looping only as many times as necessary
to process the passed in binary value), so don't worry about it being able
to handle such a large binary value. The function returns a real numeric
value up to 9999999999 after which it returns text representations of the
calculated number.
Function BinToDec(BinaryString As String) As Variant
Dim X As Integer
Const TwoToThe48 As Variant = 281474976710656#
For X = 0 To Len(BinaryString) - 1
If X > 48 Then
BinToDec = CDec(BinToDec) + Val(Mid(BinaryString, _
Len(BinaryString) - X, 1)) * _
TwoToThe48 * CDec(2 ^ (X - 48))
Else
BinToDec = CDec(BinToDec) + Val(Mid(BinaryString, _
Len(BinaryString) - X, 1)) * CDec(2 ^ X)
End If
Next
If Len(BinToDec) > 10 Then BinToDec = CStr(BinToDec)
End Function
--
Rick (MVP - Excel)
"ahmedmidany" <ahmed...@gmail.com> wrote in message
news:54419773-9aa7-42be...@m26g2000yqb.googlegroups.com...
Bernd P
I moved my webpage:
http://sulprobil.com/html/longdec2bin.html
And I provided an example file to download.
Rick's code is about 20x faster than mine, my code offers negative
numbers,
fractions and (which you might never need) even larger numbers.
Regards,
Bernd
Dana DeLouis
> Below is a UDF that will handle up to a 96-bit binary number (decimal
> value 79228162514264337593543950335) which I'm guessing is way more than
> you will ever need.<g> The code is efficient (looping only as many times
> as necessary to process the passed in binary value), so don't worry
> about it being able to handle such a large binary value. The function
> returns a real numeric value up to 9999999999 after which it returns
> text representations of the calculated number.
>
> Function BinToDec(BinaryString As String) As Variant
> Dim X As Integer
> Const TwoToThe48 As Variant = 281474976710656#
> For X = 0 To Len(BinaryString) - 1
> If X > 48 Then
> BinToDec = CDec(BinToDec) + Val(Mid(BinaryString, _
> Len(BinaryString) - X, 1)) * _
> TwoToThe48 * CDec(2 ^ (X - 48))
> Else
> BinToDec = CDec(BinToDec) + Val(Mid(BinaryString, _
> Len(BinaryString) - X, 1)) * CDec(2 ^ X)
> End If
> Next
> If Len(BinToDec) > 10 Then BinToDec = CStr(BinToDec)
> End Function
>
Hi. Just throwing out another idea. Len(BinaryString) is more of a
constant (calculated each loop), and power (ie 2^x) is sometimes
considered "slower."
This has no error checking.
Function Bin2Dec(str As String)
Dim S As String
Dim P As Long
Dim K As Variant
Dim Ans As Variant
S = StrReverse(str)
K = CDec(1)
For P = 1 To Len(S) - 1
Ans = Ans + Val(Mid$(S, P, 1)) * K
K = K * 2
Next P
Bin2Dec = Ans + Val(Mid$(S, P, 1)) * K
End Function
Sub TestIt()
Dim S As String
S = WorksheetFunction.Rept("1", 96)
Debug.Print Bin2Dec(S)
Mid(S, 3, 1) = 0
Debug.Print Bin2Dec(S)
Mid(S, 96, 1) = 0
Debug.Print Bin2Dec(S)
Mid(S, 95, 1) = 0
Debug.Print Bin2Dec(S)
Debug.Print "= = = = = = = ="
End Sub
Returns:
79228162514264337593543950335
69324642199981295394350956543
69324642199981295394350956542
69324642199981295394350956540
= = = = = = = =
Again, just an idea.
Dana DeLouis
ilya....@gmail.com
Great thanks for this. That's wonderfull - i am not a programmer and am trying to solve a logical puzzle and have ended up sitting here and looking for the way to convert 65-bit numbers from decimal to binary and back. Your solution is the best of everything that i have managed to find in internet (and have spent 3 days already for this).
Could you please be so kind as to make a UDF for converting 96-bit decimal number to binary (or 65-bit would be enough for me). As this is something that i still struggle to figure out?
Anyone else? Please help?
Ilya
jbs...@aol.com
Dim NBN As String
Dim Bin As String
5 Big = Range("A1")
AA = Len(Big)
For XX = 1 To AA
L1 = Mid(Big, XX, 1) + CRY
CRY = 0
If L1 = 0 Then
FN = "0"
GoTo 10
End If
If Int(L1 / 2) = L1 / 2 Then
FN = L1 / 2
GoTo 10
End If
If Int(L1 / 2) <> L1 / 2 Then
FN = Int(L1 / 2)
CRY = 10
GoTo 10
End If
10 NBN = NBN & FN
Next XX
If Left(NBN, 1) = "0" Then
NBN = Right(NBN, (Len(NBN) - 1))
End If
If CRY = 10 Then Bin = "1" & Bin Else Bin = "0" & Bin
Range("A1") = NBN
Range("A2") = Bin
If Len(NBN) > 0 Then
NBN = ""
CRY = 0
GoTo 5
End If