Re: Why (0.09+0.01-0.1) is not equal to 0.09+0.01-0.1 ?
- From: "Bernard Liengme" <bliengme@xxxxxxxxxxxxxxxxx>
- Date: Mon, 2 Jan 2006 10:15:11 -0400
Jerry:
Is this correct?
> ' similar to scientific notation, 1.101B2 means
> ' 1*2^2 +1*2^1 +0*2^0 +1*2^-1 = 6.5
I would have thought 11.01B2 was 1*2^2 +1*2^1 +0*2^0 +1*2^-1
But the code is only a comment so no harm is done
Happy New Year
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email
"Jerry W. Lewis" <post_a_reply@xxxxxxxxxxxxx> wrote in message
news:43B89FD7.6080506@xxxxxxxxxxxxxxxx
> 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 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) ' 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
>
.
- References:
- Re: Why (0.09+0.01-0.1) is not equal to 0.09+0.01-0.1 ?
- From: Jerry W. Lewis
- Re: Why (0.09+0.01-0.1) is not equal to 0.09+0.01-0.1 ?
- Prev by Date: Re: showing the contents of dynamic cells
- Next by Date: Re: Problem with dates
- Previous by thread: Re: Why (0.09+0.01-0.1) is not equal to 0.09+0.01-0.1 ?
- Next by thread: Re: Why (0.09+0.01-0.1) is not equal to 0.09+0.01-0.1 ?
- Index(es):
Relevant Pages
|
