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

Tech-Archive recommends: Fix windows errors by optimizing your registry

Jerry W. Lewis wrote:

GeorgeB wrote:

...  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?

...
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...

Here are much more rebust versions of the VBA code, that should handle any floating point number in Excel or VBA. The binary to decimal conversion gives full accuracy by default, but also includes rounding algorithms since users will seldom care about hundreds of figures.

Users will doubtless have to deal with line wraps, but that should be relatively easy since the code is indented.

Jerry

' Decimal/Binary conversion utilities v1.0
' (C) Copyright 2005, Jerry W. Lewis, PhD, Excel MVP
' This code may be freely used by anyone with proper attribution
'
' This code has been extensively tested, but if issues are discovered, please post them to
' the microsoft.public.excel thread "Why (0.09+0.01-0.1) is not equal to 0.09+0.01-0.1 ?"
' under my 1 Jan 2006 post
'
' Google addresses can no longer be presumed to have long-term stability,
' but the current address for this thread is
' http://groups.google.com/group/microsoft.public.excel/browse_frm/thread/9f83ca3dea38e501/6efb95785d1eaff5
'
Option Explicit

' constants for B2D function
Const digs As Long = 5 ' # decimal digits per long word to carry within B2D
Const fmt As String = "00000" ' format for word to string conversion (should contain digs zeros)
Const pow2 As Long = 14 ' largest power of 2 to multiply a partitioned mantissa by without risk of overflow
Const pow5 As Long = 6 ' largest power of 5 to multiply a partitioned mantissa by without risk of overflow

Const twoPow As Long = 2& ^ pow2
Const fivePow As Long = 5& ^ pow5
Const ten5 As Double = 10# ^ digs ' used to partition a mantissa into Long words within B2D

Function D2B(ByVal 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)
'
' written 14 Jun 2005, Jerry W. Lewis, PhD
' revised 30 Dec 2005, by Jerry W. Lewis, PhD (version 1.0) to not overflow with x > (2^45-1)*2^978
'
' similar to scientific notation, 1.101B2 means
' 1*2^2 +1*2^1 +0*2^0 +1*2^-1 = 6.5
'
' 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) ' changing an input within a function is poor form unless passed ByVal or documented as an output
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 > 1# Then ' avoid overflow with x > (2^45-1)*2^978
If x / 4 - 2 ^ (E - 1) >= 0 Then E = E + 1 ' correct possible rounding error in log2(x), as in 2^-1074
If x / 2 - 2 ^ (E - 1) < 0 Then E = E - 1 ' correct possible rounding error in log2(x)
Else
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)
End If
D2B = "1." ' leading bit assumed in mantissa under IEEE754 (what about denormal numbers?)
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

Function B2D(b As String, Optional sigFigs As Integer = 0, Optional rndMethod As Integer = 2) As String
' convert binary floating point stings (including those produced by D2B) into a decimalized string
'
' written 14 Jun 2005, Jerry W. Lewis, PhD
' revised 31 Dec 2005, by Jerry W. Lewis, PhD (version 1.0) to calculate to full accuracy (and support rounding)
'
' sigFigs defines the number of figures to return (0 ~ full accuracy)
' rndMethod defines the rounding method if sigFigs > 0
' 0 - truncate
' 1 - round 5 up
' 2 - round to nearest, with ties rounding to even (per ASTM E 29-02 section 6.4)
' aka "Bankers Rounding" aka "Statistical Rounding" aka "Unbiased Rounding"
'
' I would be grateful for any pre-1940 references to rounding method 2;
' or information about how that method came to be called "bankers rounding",
' given that I have never seen any evidence that bankers have ever used it.
'
' In principle, adequate accuracy (~28 digits) could be obtained via the VBA Decimal data type.
' In practice, the work involved in avoiding lost precision due to type conversion
' [ CDec(dbl) ~ CDec(CStr(dbl)) which sacrifices several low order bits (what about the other way?)]
' overflow, or underflow, makes that approach not worth the effort.
' The current approach is easily ported to any language,
' making it amenable to inclusion in an .xll (in progress)
'
' High precision integer arithmetic is done using
' x = sum( a[i]*c^i, i=0,..n)
' which implies that
' m*x = sum(m*a[i]*c^i, i=0,..n)
' = sum( a'[i]*c^i, i=0,..n')
' where
' a'[i] = b[i] mod c
' b[i] = m*a[i] + m*a[i-1]-b[i-1]
' for convenience of representing each part as a Long variable
' and using native arithmetic operations without overflow, c is taken to be 10^5,
' so each a[i] ~ part(i) contains integers of up to 5-digits
'
' The dimension of part() determines the capacity of the calculation.
' #VALUE! is returned if the dimension of part is overflowed.
'
If sigFigs < 0 Or rndMethod < 0 Or rndMethod > 2 Then B2D = [#VALUE!]: Exit Function
Dim part(160) As Long, carry As Long, Lo As Integer, hi As Integer, toGo As Integer
' Dimension of 160 for part() will handle IEEE double precision, including denormal numbers. DP~implied 53-bit mantissa with 11-bit exponent (bias = 1023d = 3FFh)
' Use 2309 for with 15-bit exponent (bias = 16383d = 3FFFh) of 10-byte extended precision or 16-byte IEEE quad precision
' Could limit precision and shift down to use a lower dimension, but that would add overhead and slow the routine
Dim i As Long, j As Long, last As Long, totPow As Long, mult As Long, E As Long, dig As Long
Dim sign As String, M As String
M = Trim(b) ' changing an input within a function is poor form unless passed ByVal or documented as an output
If M = "0" Then B2D = M: Exit Function
If Left(M, 1) = "-" Then sign = "-": M = Trim(Right(M, Len(M) - 1))
i = InStr(UCase(M), "B")
If i = 0& Then
E = 0&
Else
If i = Len(M) Then E = 0& Else E = CLng(Trim(Right(M, Len(M) - i))) ' will crash if not coercible (i.e. if input not in proper format)
M = Trim(Left(M, i - 1))
End If
i = InStr(UCase(M), ".")
If i <> 0& Then
E = E - (Len(M) - i) ' e.g. 0.1B0 = 1B-1
M = Left(M, i - 1) & Right(M, Len(M) - i)
End If
If Len(Replace(Replace(M, "1", ""), "0", "")) > 0 Then B2D = "Improper input format": Exit Function
Lo = 0&: hi = 0&: last = 0& ' part(0) was initialized to 0 by Dim
For i = 1& To Len(M)
If Mid(M, i, 1) = "1" Then
toGo = i - last
Do While toGo > 0&
If toGo < pow2 Then mult = 2& ^ toGo Else mult = twoPow
toGo = toGo - IIf(toGo > pow2, pow2, toGo)
GoSub Multiply
Loop
part(0) = part(0) + 1&
Lo = 0
last = i
End If
Next i
totPow = E + Len(M) - last
toGo = -totPow
If toGo > 0& Then ' M as an integer (stored in part) is x*2^toGo
Do While toGo > 0& ' so M*5^toGo = x*10^toGo correct decimal figures with shifted decimal point
If toGo < pow5 Then mult = 5& ^ toGo Else mult = fivePow ' can't use IIF because it evaluates both expressions
toGo = toGo - IIf(toGo < pow5, toGo, pow5)
GoSub Multiply
Loop
ElseIf toGo < 0& Then
Do While toGo < 0& ' so M*2^-toGo = x correct value
If toGo > -pow2 Then mult = 2& ^ -toGo Else mult = twoPow ' can't use IIF because it evaluates both expressions
toGo = toGo + IIf(toGo > -pow2, -toGo, pow2)
GoSub Multiply
Loop
End If ' Now part() has correct decimal figures, with possibly shifted decimal point
Do While part(Lo) = 0: Lo = Lo + 1: Loop
dig = Len(CStr(part(hi))) ' # digits in part(hi)
If sigFigs > 0& And rndMethod Then ' handle rounding
Dim totFigs As Long, toDrop As Long, pt As Long, ps As Long, chkRnd As String
i = 0&
Do: i = i + 1&: Loop While Right(part(Lo), i) = "0" ' i is location of 1st nonzero figure in part(lo)(1<=i<=5)
totFigs = (hi - Lo) * digs + dig - (i - 1&)
If totFigs > sigFigs Then
toDrop = totFigs - sigFigs
pt = Lo + Fix((i + toDrop - 1&) / digs)
ps = (i + toDrop - 1&) Mod digs + 1&
If ps = 0& Then pt = pt - 1&: ps = digs ' digit ps in part(pt) will be 1st digit of rounded number
If ps = 1& Then
chkRnd = Left(WorksheetFunction.Text(part(pt - 1&), fmt), 1)
Else
chkRnd = Mid(WorksheetFunction.Text(part(pt), fmt), digs + 2& - ps, 1)
End If
If chkRnd = "5" And rndMethod = 2 And toDrop = 1& Then ' tie--determine rounding direction
If CLng(Mid(WorksheetFunction.Text(part(pt), fmt), digs + 1& - ps, 1&)) Mod 2& = 0& Then chkRnd = "0" ' round down
End If
If chkRnd >= "5" Then ' round up
Lo = pt
part(Lo) = part(Lo) + 10& ^ (ps - 1&)
mult = 1&
GoSub Multiply ' to handle carry's
dig = Len(CStr(part(hi))) ' # digits in part(hi) (may have changed)
End If
End If
End If
B2D = ""
For i = Lo To hi - 1&
B2D = Format(part(i), fmt) & B2D
Next i
B2D = CStr(part(hi)) & B2D
i = Len(B2D)
If sigFigs > 0& Then B2D = Left(B2D & String(IIf(sigFigs > i, sigFigs - i, 0), "0"), sigFigs)
toGo = IIf(totPow > 0&, 0&, totPow) + digs * hi + dig - 1&
If sigFigs = 0& Then Do While Right(B2D, 1) = "0": B2D = Left(B2D, Len(B2D) - 1): Loop
B2D = sign & Left(B2D, 1) & "." & Right(B2D, Len(B2D) - 1) & "E" & toGo
Exit Function
Multiply:
carry = 0&
For j = Lo To hi
part(j) = part(j) * mult + carry
carry = Int(part(j) / ten5)
part(j) = part(j) - carry * ten5
Next j
If carry > 0& Then part(j) = carry: hi = j ' j = hi+1
If part(Lo) = 0& Then Lo = Lo + 1&
Return
End Function

Function D2D(x As Double, Optional sigFigs As Integer = 0, Optional rndMethod As Integer = 2) As String
' Convert a floating point number to a string representing the actual internal storage value
'
' written 14 Jun 2005, Jerry W. Lewis, PhD
' revised 30 Dec 2005, by Jerry W. Lewis, PhD (version 1.0) supports new rounding arguments for B2D
'
' D2D(x,17) is sufficient to uniquely determine the binary representation of x
'
D2D = B2D(D2B(x), sigFigs, rndMethod)
End Function

Function D2F(x As String) As Double
' Convert a decimal string to a floating point value
'
' written 30 Dec 2005, by Jerry W. Lewis, PhD (version 1.0)
'
' This permits use of more than 15 digits to set a value more precisely within the confines of IEEE double precision
' It is also useful to overcome Excel's behavior where Paste Special|Values will round certain values,
' confounding attempts to precisely compare accuracy of different algorithms or different Excel versions.
'
' If Len(x)>255, then Excel will return #VALUE! without executing D2F, so round long values!
' VBA will handle Len(x)>255 correctly
'
D2F = CDbl(x) ' Unlike Excel, VBA uses figures beyond the 15th to set low order bits for greater accuracy
End Function

.

Quantcast