Re: Why (0.09+0.01-0.1) is not equal to 0.09+0.01-0.1 ?

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 work***)
'
' 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

.

Loading