I have columns of decimal number (e.r 1.03, .0034,...) I
need to convert all these number to their equivalent binary
number. I am not too sure how to do this in excel.If excel
cant do it, is there any other solution to this problem?

Thankx!

On Thu, 18 Nov 2004 19:38:10 -0800, "Himu" wrote:

I have columns of decimal number (e.r 1.03, .0034,...) I
need to convert all these number to their equivalent binary
number. I am not too sure how to do this in excel.If excel
cant do it, is there any other solution to this problem?

Thankx!

This UDF written in VBA might help you, at least for positive numbers.

To enter it, <alt-F11 opens the VB Editor.

Ensure your project is highlighted in the Project Explorer window, then
Insert/Module and paste the code below into the window that opens.

To use this UDF, enter the formula:

=Baseconvert(A1,10,2,n) where n = the number of places you want to the
right of the decimal in the binary expression; and A1 is your decimal number.

Using n=20, your examples yield the following result:

1.03 1.00000111101011100001
0.0034 0.00000000110111101101

===============================
Function BaseConvert(num, FromBase As Integer, _
ToBase As Integer, Optional DecPlace As Long) _
As String

'by Ron Rosenfeld

Dim LDI As Integer 'Leading Digit Index
Dim i As Integer, j As Integer
Dim Temp, Temp2
Dim Digits()
Dim r
Dim DecSep As String

DecSep = Application.International(xlDecimalSeparator)

On Error GoTo HANDLER

If FromBase 62 Or ToBase 62 _
Or FromBase < 2 Or ToBase < 2 Then
BaseConvert = "Base out of range"
Exit Function
End If

If InStr(1, num, "E") And FromBase = 10 Then
num = CDec(num)
End If

'Convert to Base 10
LDI = InStr(1, num, DecSep) - 2
If LDI = -2 Then LDI = Len(num) - 1

j = LDI

Temp = Replace(num, DecSep, "")
For i = 1 To Len(Temp)
Temp2 = Mid(Temp, i, 1)
Select Case Temp2
Case "A" To "Z"
Temp2 = Asc(Temp2) - 55
Case "a" To "z"
Temp2 = Asc(Temp2) - 61
End Select
If Temp2 = FromBase Then
BaseConvert = "Invalid Digit"
Exit Function
End If
r = CDec(r + Temp2 * FromBase ^ j)
j = j - 1
Next i

If r < 0 Then LDI = Fix(CDec(Log(r) / Log(ToBase)))
If r < 1 Then LDI = 0

ReDim Digits(LDI)

For i = UBound(Digits) To 0 Step -1
Digits(i) = Format(Fix(r / ToBase ^ i))
r = CDbl(r - Digits(i) * ToBase ^ i)
Select Case Digits(i)
Case 10 To 35
Digits(i) = Chr(Digits(i) + 55)
Case 36 To 62
Digits(i) = Chr(Digits(i) + 61)
End Select
Next i

Temp = StrReverse(Join(Digits, "")) 'Integer portion
ReDim Digits(DecPlace)

If r < 0 Then
Digits(0) = DecSep
For i = 1 To UBound(Digits)
Digits(i) = Format(Fix(r / ToBase ^ -i))
r = CDec(r - Digits(i) * ToBase ^ -i)
Select Case Digits(i)
Case 10 To 35
Digits(i) = Chr(Digits(i) + 55)
Case 36 To 62
Digits(i) = Chr(Digits(i) + 61)
End Select
Next i
End If

BaseConvert = Temp & Join(Digits, "")

Exit Function
HANDLER: MsgBox ("Error: " & Err.Number & " " & Err.Description & vbLf & _
"Number being converted: " & num)

End Function
=============================


--ron

Hi
use
DEC2BIN
from the Analysis Toolpak Addin

--
Regards
Frank Kabel
Frankfurt, Germany

"Himu" schrieb im Newsbeitrag
...
I have columns of decimal number (e.r 1.03, .0034,...) I
need to convert all these number to their equivalent binary
number. I am not too sure how to do this in excel.If excel
cant do it, is there any other solution to this problem?

Thankx!

Hi Frank:

Could you instruct me on how to use the Analysis Toolpak
Addin. Thankx!

Himu

-----Original Message-----
Hi
use
DEC2BIN
from the Analysis Toolpak Addin

--
Regards
Frank Kabel
Frankfurt, Germany

"Himu" schrieb im Newsbeitrag
...
I have columns of decimal number (e.r 1.03, .0034,...) I
need to convert all these number to their equivalent binary
number. I am not too sure how to do this in excel.If excel
cant do it, is there any other solution to this problem?

Thankx!

.

If you didn't find it already, it's in Tools |Add-Ins. Check the Analysis
Toolpak. Then, you can type the formula like any other.

tj

"Himu" wrote:

Hi Frank:

Could you instruct me on how to use the Analysis Toolpak
Addin. Thankx!

Himu

-----Original Message-----
Hi
use
DEC2BIN
from the Analysis Toolpak Addin

--
Regards
Frank Kabel
Frankfurt, Germany

"Himu" schrieb im Newsbeitrag
...
I have columns of decimal number (e.r 1.03, .0034,...) I
need to convert all these number to their equivalent binary
number. I am not too sure how to do this in excel.If excel
cant do it, is there any other solution to this problem?

Thankx!

.

On Fri, 19 Nov 2004 07:04:20 +0100, "Frank Kabel"
wrote:

Hi
use
DEC2BIN
from the Analysis Toolpak Addin

Frank,

He specified numbers like 1.03; 0.0034 and DEC2BIN only handles integer
values.


--ron

Hi Ron. I really like your code. Works great. ;)
May I make an observation? In the following 3 lines...

CDec(Log(r) / Log(ToBase)
CDec(r - Digits(i) * ToBase ^ -i)
CDec(r + Temp2 * FromBase ^ j)

Although these show up as "Decimal", and perhaps with more than 15 digits,
I just want to point out that their "precision" will not be more than the
standard 15 digits. Excel doesn't support Log & "^" at this time. However,
it can still be an advantage to use CDec as your code does despite this
limitation.
As a side note, I have been stuck on a Log function for a long time. I've
have experimented with many algorithms and Series expansions, but nothing
that I'm really happy with. For equations of the form FromBase ^j, (j is
increased in a loop) the usual technique is to start with a separate
variable (say t) to hold this number, and on each pass in the
loop...t=t*FromBase. Or something similar.
Anyway, great code. Just thought I'd pass on an observation. ;)
--
Dana DeLouis
Win XP & Office 2003


"Ron Rosenfeld" wrote in message
...
On Thu, 18 Nov 2004 19:38:10 -0800, "Himu"
wrote:

I have columns of decimal number (e.r 1.03, .0034,...) I
need to convert all these number to their equivalent binary
number. I am not too sure how to do this in excel.If excel
cant do it, is there any other solution to this problem?

Thankx!

This UDF written in VBA might help you, at least for positive numbers.

To enter it, <alt-F11 opens the VB Editor.

Ensure your project is highlighted in the Project Explorer window, then
Insert/Module and paste the code below into the window that opens.

To use this UDF, enter the formula:

=Baseconvert(A1,10,2,n) where n = the number of places you want to the
right of the decimal in the binary expression; and A1 is your decimal
number.

Using n=20, your examples yield the following result:

1.03 1.00000111101011100001
0.0034 0.00000000110111101101

===============================
Function BaseConvert(num, FromBase As Integer, _
ToBase As Integer, Optional DecPlace As Long) _
As String

'by Ron Rosenfeld

Dim LDI As Integer 'Leading Digit Index
Dim i As Integer, j As Integer
Dim Temp, Temp2
Dim Digits()
Dim r
Dim DecSep As String

DecSep = Application.International(xlDecimalSeparator)

On Error GoTo HANDLER

If FromBase 62 Or ToBase 62 _
Or FromBase < 2 Or ToBase < 2 Then
BaseConvert = "Base out of range"
Exit Function
End If

If InStr(1, num, "E") And FromBase = 10 Then
num = CDec(num)
End If

'Convert to Base 10
LDI = InStr(1, num, DecSep) - 2
If LDI = -2 Then LDI = Len(num) - 1

j = LDI

Temp = Replace(num, DecSep, "")
For i = 1 To Len(Temp)
Temp2 = Mid(Temp, i, 1)
Select Case Temp2
Case "A" To "Z"
Temp2 = Asc(Temp2) - 55
Case "a" To "z"
Temp2 = Asc(Temp2) - 61
End Select
If Temp2 = FromBase Then
BaseConvert = "Invalid Digit"
Exit Function
End If
r = CDec(r + Temp2 * FromBase ^ j)
j = j - 1
Next i

If r < 0 Then LDI = Fix(CDec(Log(r) / Log(ToBase)))
If r < 1 Then LDI = 0

ReDim Digits(LDI)

For i = UBound(Digits) To 0 Step -1
Digits(i) = Format(Fix(r / ToBase ^ i))
r = CDbl(r - Digits(i) * ToBase ^ i)
Select Case Digits(i)
Case 10 To 35
Digits(i) = Chr(Digits(i) + 55)
Case 36 To 62
Digits(i) = Chr(Digits(i) + 61)
End Select
Next i

Temp = StrReverse(Join(Digits, "")) 'Integer portion
ReDim Digits(DecPlace)

If r < 0 Then
Digits(0) = DecSep
For i = 1 To UBound(Digits)
Digits(i) = Format(Fix(r / ToBase ^ -i))
r = CDec(r - Digits(i) * ToBase ^ -i)
Select Case Digits(i)
Case 10 To 35
Digits(i) = Chr(Digits(i) + 55)
Case 36 To 62
Digits(i) = Chr(Digits(i) + 61)
End Select
Next i
End If

BaseConvert = Temp & Join(Digits, "")

Exit Function
HANDLER: MsgBox ("Error: " & Err.Number & " " & Err.Description & vbLf & _
"Number being converted: " & num)

End Function
=============================


--ron

Hi Ron
good point. Overlooked this part of the question.

Frank
P.S.: As Dana I like your code <vbg

"Ron Rosenfeld" wrote:

On Fri, 19 Nov 2004 07:04:20 +0100, "Frank Kabel"
wrote:

Hi
use
DEC2BIN
from the Analysis Toolpak Addin

Frank,

He specified numbers like 1.03; 0.0034 and DEC2BIN only handles integer
values.


--ron

works like a majic ron.... thankx a lot for ur help...
still wondering on how can i be able to represent negative
decimal number to binary... let me know if u find out
anything on that....

On Fri, 19 Nov 2004 09:07:10 -0500, "Dana DeLouis"
wrote:

Hi Ron. I really like your code. Works great. ;)
May I make an observation? In the following 3 lines...

CDec(Log(r) / Log(ToBase)
CDec(r - Digits(i) * ToBase ^ -i)
CDec(r + Temp2 * FromBase ^ j)

Although these show up as "Decimal", and perhaps with more than 15 digits,
I just want to point out that their "precision" will not be more than the
standard 15 digits. Excel doesn't support Log & "^" at this time. However,
it can still be an advantage to use CDec as your code does despite this
limitation.
As a side note, I have been stuck on a Log function for a long time. I've
have experimented with many algorithms and Series expansions, but nothing
that I'm really happy with. For equations of the form FromBase ^j, (j is
increased in a loop) the usual technique is to start with a separate
variable (say t) to hold this number, and on each pass in the
loop...t=t*FromBase. Or something similar.
Anyway, great code. Just thought I'd pass on an observation. ;)

Thank you for that observation. I appreciate it. I had fun researching it. I
had always wondered about the limitations of the ATP add-in with regard to
handling fractions on the conversions. So I learned something. And after
figuring out the basics, it was relatively simple to extend the algorithm to
include all the bases that could be represented by the alphabet :-).

It seems to me that when I was doing some basic testing, there were some
numbers that appeared to have more accurate results with CDec vs CDbl, but I
don't know enough about number (or computer) theory to be able to characterize
it any better than that statement. (self-taught).

Best,

--ron

On Fri, 19 Nov 2004 07:57:50 -0800, "Himu" wrote:

works like a majic ron.... thankx a lot for ur help...
still wondering on how can i be able to represent negative
decimal number to binary... let me know if u find out
anything on that....

That gets complicated.

First you have to decide on how you want to represent negative binary numbers
as well as, for the sake of this UDF, negative numbers in other bases.

For binary numbers, you can use one's complement, or two's complement.

But I chose not to get into that. It shouldn't be too much of a problem to
implement, though, if you wanted to. And there's plenty of information you
could get by doing a Google search for "representing negative binary numbers"


--ron

On Fri, 19 Nov 2004 07:11:04 -0800, "Frank Kabel"
wrote:

Hi Ron
good point. Overlooked this part of the question.

Frank
P.S.: As Dana I like your code <vbg

Thank you.


--ron

On Fri, 19 Nov 2004 09:07:10 -0500, "Dana DeLouis"
wrote:

As a side note, I have been stuck on a Log function for a long time. I've
have experimented with many algorithms and Series expansions, but nothing
that I'm really happy with. For equations of the form FromBase ^j, (j is
increased in a loop) the usual technique is to start with a separate
variable (say t) to hold this number, and on each pass in the
loop...t=t*FromBase. Or something similar.

Ah, how the mind goes. *Many* (35?) years ago, someone introduced me to a
really neat Log algorithm that we implemented in machine language on a 12 bit
machine. Unfortunately, I have neither my notes nor my memory to reproduce it
<sigh


--ron

"Ron Rosenfeld" wrote...
....
This UDF written in VBA might help you, at least for positive numbers.
....

Presumably all it'd take to handle negative numbers would be

IF(x<0,"-","")&SomeUDF(ABS(x),...)

but that should be simple enough to add to the udf.


Function BaseConvert(num, FromBase As Integer, _
ToBase As Integer, Optional DecPlace As Long) _
As String

'by Ron Rosenfeld
....
On Error GoTo HANDLER
....

Open-ended error handling is questionable.


Maybe it's my prejudices, but exponentiation and logarithms are unnecessary
and inefficient for simple base conversion.

As an alternative, a two-function approach.


Private Const DIGITS As String = "0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ"
Private Const MAXRADIX As Long = 36


Function q2d(ByVal q As String, r As Long) As Double
Dim i As Long, j As Long, k As Long, n As Long
Dim neg As Boolean, sep As String, t As Double

If r < 2 Or r MAXRADIX Then GoTo Die

sep = Application.International(xlDecimalSeparator)

q = Trim(q)
If Left(q, 1) = "-" Then
neg = True
q = Mid(q, 2)
End If
n = Len(q)

k = InStr(1, q, sep)

If k 1 Then
For i = 1 To k - 1
j = InStr(1, DIGITS, Mid(q, i, 1), vbTextCompare)
If j < 1 Or j r Then GoTo Die
q2d = q2d * r + j - 1#
Next i
End If

If k 0 Then
For i = n To k + 1 Step -1
j = InStr(1, DIGITS, Mid(q, i, 1), vbTextCompare)
If j < 1 Or j r Then GoTo Die
t = t / r + j - 1#
Next i
q2d = q2d + t / r
End If

If neg Then q2d = -q2d

Exit Function

Die:
q2d = 0
Err.Raise Number:=9999, Source:="q2d", Description:="Invalid"
End Function


Function d2q(d As Double, r As Long, Optional p As Long = 0) As String
Dim neg As Boolean, a As Double, b As Double, c As Double

If r < 2 Or r MAXRADIX Or p < 0 Then GoTo Die

If d < 0 Then neg = True

c = Abs(d)
a = Int(c)
b = c - a

If a = 0 Then d2q = "0"

Do While a 0
c = a Mod r
d2q = Mid(DIGITS, c + 1, 1) & d2q
a = (a - c) / r
Loop

If neg Then d2q = "-" & d2q

If b 0 And p 0 Then
d2q = d2q & Application.International(xlDecimalSeparator)
For p = p To 1 Step -1
b = b * r
d2q = d2q & Mid(DIGITS, Int(b) + 1, 1)
b = b - Int(b)
Next p
End If

Exit Function

Die:
d2q = ""
Err.Raise Number:=9999, Source:="d2q", Description:="Invalid"
End Function

On Fri, 19 Nov 2004 23:08:49 -0800, "Harlan Grove" wrote:

"Ron Rosenfeld" wrote...
...
This UDF written in VBA might help you, at least for positive numbers.
...

Presumably all it'd take to handle negative numbers would be

IF(x<0,"-","")&SomeUDF(ABS(x),...)

but that should be simple enough to add to the udf.



That's what I do if I need to outside the UDF.

But the OP was talking about binary numbers. My (limited) understanding is
that there are several methods of representing negative numbers in binary,
including one's complement, two's complement, and, with the fractional part,
one can represent it with a signed integer and a positive fractional part.



Function BaseConvert(num, FromBase As Integer, _
ToBase As Integer, Optional DecPlace As Long) _
As String

'by Ron Rosenfeld
...
On Error GoTo HANDLER
...

Open-ended error handling is questionable.


Maybe it's my prejudices, but exponentiation and logarithms are unnecessary
and inefficient for simple base conversion.

You are a better judge of efficiency than I. But I prefer numeric approaches.


As an alternative, a two-function approach.



Limited testing suggests our results are similar, and identical within the
precision limits of Excel.


--ron

Ron Rosenfeld wrote...
On Fri, 19 Nov 2004 23:08:49 -0800, "Harlan Grove"
wrote:
"Ron Rosenfeld" wrote...
...
This UDF written in VBA might help you, at least for positive
numbers.
...

Presumably all it'd take to handle negative numbers would be

IF(x<0,"-","")&SomeUDF(ABS(x),...)

but that should be simple enough to add to the udf.

That's what I do if I need to outside the UDF.

But the OP was talking about binary numbers. My (limited)
understanding is
that there are several methods of representing negative numbers in
binary,
including one's complement, two's complement, and, with the
fractional part,
one can represent it with a signed integer and a positive fractional
part.
....

All depends on whether negatives would be represented with a negative
sign or in some form of binary encoding. Fractional parts would be
trivial if one assumes fixed point representation.

Maybe it's my prejudices, but exponentiation and logarithms are
unnecessary
and inefficient for simple base conversion.

You are a better judge of efficiency than I. But I prefer numeric
approaches.

Unnecessary exponentiation and logarithms are PURE EVIL in numeric
programming, as you should recall if you were doing numeric
programming back in the Dark Ages before FPUs were common in PCs
either as co-processors or included on the same chip as the CPU. It's
not just the speed performance, it's also the precision that suffers.
Take discount calculations, for example. An approach similar to yours

v = 1 / 1.05
dv = 0
For i = 0 To N
dv = dv + cf(i) * v ^ i
Next i

may seem like it does a reasonable job, but an approach similar to
mine

v = 1 / 1.05
dv = 0
For i = N To 0 Step -1
dv = dv * v + cf(i)
Next i

will do a much better job of preserving precision. But precision isn't
really at issue here. Change of radix is a simple procedure. It should
have a simple implementation.