GeorgeB wrote:
....
I'm sure that is a typo, but I understand completely. How do you
easily come up with these conversions ... I understand that in concept
it will be 1/16 + 1/64 + 1/128 + 1/256 ..... but how is the decimal
fraction to binary performed?
I do it in Maple,
http://groups-beta.google.com/group/microsoft.public.excel.misc/msg/b824897bcc8a2c71
which lets me get the decimal equivalent of the binary approximation to
full precision. However less precision would suffice, since 17 digits
would uniquely identify an IEEE double precision binary representation.
The following discusses a first stab at an Excel based approach.
It is not difficult to calculate binary representations; you just have
to make sure that you avoid misguided "helpfulness" such as the subject
of this thread. The following VBA function seems to do the trick (you
will probably have to correct line wraps, but all are lines within the
function are indented, so identifying line wrap should be straightforward).
Function D2B(x As Double) As String
' convert floating point number to its binary representation with 53
mantissa bits
' (IEEE 754 has 52 explicit and 1 assumed bit in the mantissa for
double precision)
'
' similar to scientific notation, 1.101B2 means
' 1*2^2 +1*2^1 +0*2^0 +1*2^-1 = 6.5
'
' written 14 Jun 2005, by Jerry W. Lewis, PhD
'
' handles denormal numbers (supported by VBA, but not worksheet)
'
' References:
'
http://support.microsoft.com/kb/78113
'
http://www.cpearson.com/excel/rounding.htm
'
http://grouper.ieee.org/groups/754/
Dim sign As String, E As Long, i As Long, R As Double, a As Double
If x = 0 Then D2B = "0": Exit Function
If x < 0 Then sign = "-": x = Abs(x)
E = Int(Log(x) / Log(2#)) ' log2(x), the exponent of the binary
representation (Int needed because \ converts numerator & denominator to
integers and type coercion rounds)
If x - 2 ^ (E + 1) >= 0 Then E = E + 1 ' correct possible rounding
error in log2(x), as in 2^-1074
If x - 2 ^ E < 0 Then E = E - 1 ' correct possible rounding
error in log2(x)
D2B = "1." ' leading bit assumed in mantissa under
IEEE754
R = x - 2 ^ E ' remainder to be approximated
For i = E - 1 To IIf(E - 52 > -1074, E - 52, -1074) Step -1 '
1.B-1074 is smallest denormal number
If 2 ^ i <= R Then
D2B = D2B & "1"
R = R - 2 ^ i
Else
D2B = D2B & "0"
End If
Next
D2B = sign & D2B & "B" & E
End Function
Going the other way (binary to decimal) is much trickier. You can use
the VBA Decimal data type to carry 28 figures, but you have to be
careful to avoid truncation in type conversions and to avoid overflow or
underflow since the Decimal data type has fixed precision with no
scientific notation. The following function works reasonably well for
decimal exponents in the -5 to 15 range. In principal it could be
generalized to return scientific notation over the full range of
representable numbers, but I have not had time to do that yet. Again
watch for wrapping of long lines. Some of the trailing comments are
debug notes to myself for cases where I detected problems -- sorry about
that -- if you hadn't asked I would't have shown this until it was ready
for prime time.
Function B2D(b As String) As String
' convert binary floating point representation of D2B into a
decimalized string of
' up to 28 digits (assuming that the number is within the limits of
the VBA Decimal data type)
'
' written 14 Jun 2005, by Jerry W. Lewis, PhD
negative integer powers of 2
Dim sign As String, E As Long, M As String, R As String, i As
Double, D As Variant, dig As Long, c As Variant
b = Trim(b)
If b = "0" Then B2D = b: Exit Function
If Left(b, 1) = "-" Then sign = "-": b = Trim(Right(b, Len(b) - 1))
i = InStr(UCase(b), "B")
If i = 0 Or i = Len(b) Or Left(b, 2) <> "1." Then B2D = "Improper
input format": Exit Function
M = Left(b, i - 1): M = Right(M, Len(M) - 2)
If Len(Replace(Replace(M, "1", ""), "0", "")) > 0 Then B2D =
"Improper input format": Exit Function
E = CDbl(Right(b, Len(b) - i)) ' will crash if not coercible (i.e.
if input not in proper format)
c = CDec(2# ^ 49) ' largest power of 2 that will convert exactly
from Dbl to Dec (16+ digit #'s round to 15 digits before conversion,
even if exactly representable)
D = c * CDec(2 ^ 3) ' conversion to decimal truncates rather than
rounding, so add integers to avoid accumulating truncation errors
For i = 1 To Len(M)
If Mid(M, i, 1) = 1 Then D = D + IIf(i < 3, c * CDec(2 ^ (3 -
i)), CDec(2# ^ (52 - i)))
Next
' D = B2D * 2^(52-E)
If E < 0 Then ' 79,228,162,514,264,337,593,543,950,335 largest w/
29 figs else 28 figs -- add trailing zeros
' D * CDec(5 ^ -E) overflows because 4722366482869645*5^20 =
4.5035996273705E+29 in D2D(1E-6)
' only works for E >= -18
If E >= -18 Then
D = D * CDec(5 ^ -E) / (c * CDec(2 ^ 3)) ' shift decimal
point to not loose precision
Else
D = D / CDec(10 ^ 15) * IIf(E >= -21, CDec(5 ^ -E), CDec(5
^ 21) * CDec(5 ^ (-E - 21)))
E = E + 15
Dim l10 As Double
l10 = Fix(Log(CDbl(D)) / Log(10#))
D = D * CDec(10 ^ (27 - l10)) / (c * CDec(2 ^ 3))
E = E - (27 - l10)
End If
B2D = CStr(D)
i = InStr(B2D, ".")
If i = 0 Then i = Len(B2D) + 1 ' added 7/15/05 to handle
integer powers of 2
B2D = Replace(CStr(D), ".", "")
B2D = "0." & String(1 - (i + E), "0") & B2D
Else
If E >= 52 Then
D = D * CDec(2# ^ (E - 52))
Else
D = D / IIf(E < 3, c * CDec(2 ^ (3 - E)), CDec(2# ^ (52 - E)))
End If
B2D = CStr(D)
End If
B2D = sign & B2D
End Function
In most instance you will probably only be interested in the decimal
representation of the binary approximation, so the following wrapper
function converts to binary and then back again in one step.
Function D2D(x As Double) As String
' Convert a floating point number to a string representing the actual
internal storage value
'
' written 14 Jun 2005, by Jerry W. Lewis, PhD
D2D = B2D(D2B(x))
End Function
Jerry
