Hi,
Does anybody know why in Excel2000 the
(0.09+0.01-0.1) = -1,38778E-17
while
0.09+0.01-0.1 = 0 (as taught in schools)
The same error you get with (0.09+0.01)-0.1.
I checked it in on Intel Celeron 400MHz and Pentium IV with on two operation systems: W2K and W98. Seems to be Excel bug.
I found it incementing a Double variable in VBA.
Does anybody know how to get rid of that?
Thanks in advance
Valdi
Using XL 2002 on Win XP
I get zero when typing =.09+.01-.1 into a cell
If you are producing any of these numbers through arithmetic operations you
may not really have .09 for instance. It might be .08999997 or .09000002.
If you want to limit these to 2 decimal places, round them
"Valdi" wrote:
>
> Hi,
>
> Does anybody know why in Excel2000 the
>
> (0.09+0.01-0.1) = -1,38778E-17
>
> while
>
> 0.09+0.01-0.1 = 0 (as taught in schools)
>
> The same error you get with (0.09+0.01)-0.1.
>
> I checked it in on Intel Celeron 400MHz and Pentium IV with on two
> operation systems: W2K and W98. Seems to be Excel bug.
>
> I found it incementing a Double variable in VBA.
>
> Does anybody know how to get rid of that?
>
> Thanks in advance
>
> Valdi
>
>
> --
> Valdi
> ------------------------------------------------------------------------
> Valdi's Profile: http://www.excelforum.com/member.php...o&userid=26338
> View this thread: http://www.excelforum.com/showthread...hreadid=396049
>
>
Excel and almost all other computer software does binary math. In
binary, none of your input numbers have exact representations and must
be approximated (much as 1/3 must be approximated in decimal). With
approximate inputs, that the output is only approximate should be no
surprise.
The correct sum of approximations to 0.09 and 0.01 is
0.09999999999999999167332731531132594682276248931884765625
vs. the approximation to 0.1 which is
0.1000000000000000055511151231257827021181583404541015625
Do the math, the correct difference is
-1.387778780781445675529539585113525390625E-17
which Excel correctly reports to its documented 15 digit limit as
-1.38777878078145E-17
This could occur in decimal as well as binary, just with different
numbers. Consider a hypothetical computer that does decimal math to 4
figures. In that hypothetical computer,
=(1/3)+(1/3)
would be 0.6666 since the best 4-digit decimal approximation to 1/3 is
0.3333. But the best 4-digit decimal approximation to 2/3 is 0.6667 so that
=(1/3)+(1/3)-(2/3)
would be -0.0001 instead of zero, much like =(0.09+0.01-0.1) in Excel.
The difference between =(0.09+0.01-0.1) and =0.09+0.01-0.1 is that in
the second form, the last operation is a subtraction between numbers
that agree to 15 decimal digits. In that latter case, Excel doesn't do
the math; it assumes (correctly in this case) that the actual non-zero
result is only due to binary approximations, and therefore arbitrarily
returns zero even though the actual difference between the inputs is
nonzero. This fuzz factor was introduced with Excel 97
http://support.microsoft.com/kb/7811...22120121120120
but may cause more questions than it answers, since (as you discovered)
it results in apparently inconsistent numeric results.
Excel does not arbitrarily zero if the subtraction is not the final
operation (as when the formula is wrapped in parentheses) because that
would introduce inaccuracy if the assumption behind zeroing the result
were not true. The IEEE 754 standard defines the precision used by
Excel and most other software. The actual limit is 53 bits (52 explicit
and 1 assumed)
http://www.cpearson.com/excel/rounding.htm
The net result is that it takes 17 digits to uniquely identify a
particular binary approximation. As a result there are several (>10)
distinct binary numbers that all have the same 15-digit decimal
representation. There are a number of ways that you could have
differences that large that you wouldn't want treated as zero.
An easier way to think of these issues is to use Excel's documented
15-digit limit as a guide, so that your problem becomes
0.0900000000000000??
+0.0100000000000000??
-0.100000000000000???
---------------------
0.000000000000000???
which is consistent with
-0.00000000000000001388
Given that the issue is approximation to the inputs, not a problem with
the subsequent math, you can control the output by rounding the final
result when appropriate. Alternately, a standard approach for the last
half century (long before Excel) is to test whether results are suitably
close, instead of exactly equal. A third approach would be to do
integer math, since integers are exactly representable, so that
=(9+1-10)/100 would return zero.
Jerry
Valdi wrote:
> Hi,
>
> Does anybody know why in Excel2000 the
>
> (0.09+0.01-0.1) = -1,38778E-17
>
> while
>
> 0.09+0.01-0.1 = 0 (as taught in schools)
>
> The same error you get with (0.09+0.01)-0.1.
>
> I checked it in on Intel Celeron 400MHz and Pentium IV with on two
> operation systems: W2K and W98. Seems to be Excel bug.
>
> I found it incementing a Double variable in VBA.
>
> Does anybody know how to get rid of that?
>
> Thanks in advance
>
> Valdi
Valdi's point was that while
=0.09+0.01-0.1
returns zero,
=(0.09+0.01-0.1)
does not. See my other reply for an explanation.
Jerry
Duke Carey wrote:
> Using XL 2002 on Win XP
>
> I get zero when typing =.09+.01-.1 into a cell
>
> If you are producing any of these numbers through arithmetic operations you
> may not really have .09 for instance. It might be .08999997 or .09000002.
>
> If you want to limit these to 2 decimal places, round them
On Tue, 16 Aug 2005 08:01:53 -0400, "Jerry W. Lewis"
<post_a_reply@no_e-mail.com> wrote:
>Excel and almost all other computer software does binary math. In
>binary, none of your input numbers have exact representations and must
>be approximated (much as 1/3 must be approximated in decimal). With
>approximate inputs, that the output is only approximate should be no
>surprise.
>
>The correct sum of approximations to 0.09 and 0.01 is
> 0.09999999999999999167332731531132594682276248931884765625
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?
>vs. the approximation to 0.1 which is
> 0.1000000000000000055511151231257827021181583404541015625
>Do the math, the correct difference is
> -1.387778780781445675529539585113525390625E-17
>which Excel correctly reports to its documented 15 digit limit as
> -1.38777878078145E-17
>
>This could occur in decimal as well as binary, just with different
>numbers. Consider a hypothetical computer that does decimal math to 4
>figures. In that hypothetical computer,
> =(1/3)+(1/3)
>would be 0.6666 since the best 4-digit decimal approximation to 1/3 is
>0.3333. But the best 4-digit decimal approximation to 2/3 is 0.6667 so that
> =(1/3)+(1/3)-(2/3)
>would be -0.0001 instead of zero, much like =(0.09+0.01-0.1) in Excel.
>
>The difference between =(0.09+0.01-0.1) and =0.09+0.01-0.1 is that in
>the second form, the last operation is a subtraction between numbers
>that agree to 15 decimal digits. In that latter case, Excel doesn't do
>the math; it assumes (correctly in this case) that the actual non-zero
>result is only due to binary approximations, and therefore arbitrarily
>returns zero even though the actual difference between the inputs is
>nonzero. This fuzz factor was introduced with Excel 97
> http://support.microsoft.com/kb/7811...22120121120120
>but may cause more questions than it answers, since (as you discovered)
>it results in apparently inconsistent numeric results.
>
>Excel does not arbitrarily zero if the subtraction is not the final
>operation (as when the formula is wrapped in parentheses) because that
>would introduce inaccuracy if the assumption behind zeroing the result
>were not true. The IEEE 754 standard defines the precision used by
>Excel and most other software. The actual limit is 53 bits (52 explicit
>and 1 assumed)
> http://www.cpearson.com/excel/rounding.htm
>The net result is that it takes 17 digits to uniquely identify a
>particular binary approximation. As a result there are several (>10)
>distinct binary numbers that all have the same 15-digit decimal
>representation. There are a number of ways that you could have
>differences that large that you wouldn't want treated as zero.
>
>An easier way to think of these issues is to use Excel's documented
>15-digit limit as a guide, so that your problem becomes
> 0.0900000000000000??
> +0.0100000000000000??
> -0.100000000000000???
> ---------------------
> 0.000000000000000???
>which is consistent with
> -0.00000000000000001388
>
>Given that the issue is approximation to the inputs, not a problem with
>the subsequent math, you can control the output by rounding the final
>result when appropriate. Alternately, a standard approach for the last
>half century (long before Excel) is to test whether results are suitably
>close, instead of exactly equal. A third approach would be to do
>integer math, since integers are exactly representable, so that
>=(9+1-10)/100 would return zero.
>
>Jerry
>
>Valdi wrote:
>
>> Hi,
>>
>> Does anybody know why in Excel2000 the
>>
>> (0.09+0.01-0.1) = -1,38778E-17
>>
>> while
>>
>> 0.09+0.01-0.1 = 0 (as taught in schools)
>>
>> The same error you get with (0.09+0.01)-0.1.
>>
>> I checked it in on Intel Celeron 400MHz and Pentium IV with on two
>> operation systems: W2K and W98. Seems to be Excel bug.
>>
>> I found it incementing a Double variable in VBA.
>>
>> Does anybody know how to get rid of that?
>>
>> Thanks in advance
>>
>> Valdi
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/...24897bcc8a2c71
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
On Tue, 16 Aug 2005 22:56:12 -0400, "Jerry W. Lewis"
<post_a_reply@no_e-mail.com> wrote:
>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/...24897bcc8a2c71
>
>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.
Thanks; I'll play with that. My son has a math degree and has Maple;
I'll hit him for a "little lernin". I think I remember pieces of this
from my EE degree in 1972, also.
GeorgeB wrote:
> Thanks; I'll play with that. My son has a math degree and has Maple;
> I'll hit him for a "little lernin". I think I remember pieces of this
> from my EE degree in 1972, also.
You're welcome. Suggestions would also be welcomed.
Jerry
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/micro...fb95785d1eaff5
'
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
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@no_e-mail.com> wrote in message
news:43B89FD7.6080506@no_e-mail.com...
> 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/micro...fb95785d1eaff5
> '
> 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
>
Hi Bernard,
If you enter 1.101E2 or 11.01E1, Excel will interpret both as
10^2+10^1+10^-1 = 110.1
Analogously
=B2D("1.101B2")
=B2D("11.01B1")
=B2D("110.1B0")
=B2D("110.1B")
=B2D("110.1")
all return "6.5E0" since the values are interpreted as 2^2+2^1+2^-1 = 6.5
I believe that the comment is correct as written, and is consistent with
standard notation, but would certainly consider counter arguments.
=D2B(6.5) follows the first form, returning
"1.1010000000000000000000000000000000000000000000000000B2" padded to 53 bits
because Excel and VBA default to their maximum (double) precision. Perhaps I
should have included an option to return a 24bit output (single precision),
but you can pass single precision variables to D2B and manually truncate the
result.
Any other feedback?
Jerry
"Bernard Liengme" wrote:
> 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
Many thanks for explanation. I did not understand the meaning of B2. I
should have looked at it as analogous to E2 but read is a meaning binary-two
(too much C2H5OH this season?)
best wishes
--
Bernard
"Jerry W. Lewis" <[email protected]> wrote in message
news:[email protected]...
> Hi Bernard,
>
> If you enter 1.101E2 or 11.01E1, Excel will interpret both as
> 10^2+10^1+10^-1 = 110.1
>
> Analogously
> =B2D("1.101B2")
> =B2D("11.01B1")
> =B2D("110.1B0")
> =B2D("110.1B")
> =B2D("110.1")
> all return "6.5E0" since the values are interpreted as 2^2+2^1+2^-1 = 6.5
>
> I believe that the comment is correct as written, and is consistent with
> standard notation, but would certainly consider counter arguments.
>
> =D2B(6.5) follows the first form, returning
> "1.1010000000000000000000000000000000000000000000000000B2" padded to 53
> bits
> because Excel and VBA default to their maximum (double) precision.
> Perhaps I
> should have included an option to return a 24bit output (single
> precision),
> but you can pass single precision variables to D2B and manually truncate
> the
> result.
>
> Any other feedback?
>
> Jerry
>
> "Bernard Liengme" wrote:
>> 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
Glad we're on the same page. I hope you find the code useful.
Happy New Year! (but watch out for that "holiday cheer")
Jerry
"Bernard Liengme" wrote:
> Many thanks for explanation. I did not understand the meaning of B2. I
> should have looked at it as analogous to E2 but read is a meaning binary-two
> (too much C2H5OH this season?)
> best wishes
> --
> Bernard
"Jerry W. Lewis" wrote:
....
> 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.
....
v1.0 assumes a period to be the decimal separator. With very minor changes,
it can adapt to whatever regional settings are in place.
In D2B, replace the line
D2B = "1."
with
D2B = Left(3# / 2#, 2)
In B2D replace the lines
i = InStr(UCase(M), ".")
"Jerry W. Lewis" wrote:
....
> 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.
....
v1.0 assumes a period to be the decimal separator. With very minor changes,
it can adapt to whatever regional settings are in place.
In D2B, replace the line
D2B = "1."
with
D2B = Left(3# / 2#, 2)
In B2D replace the lines
i = InStr(UCase(M), ".")
B2D = sign & Left(B2D, 1) & "." & Right(B2D, Len(B2D) - 1) & "E" & toGo
with
i = InStr(UCase(M), Mid(1# / 2#, 2, 1))
B2D = sign & Left(B2D, 1) & Mid(1# / 2#, 2, 1) & Right(B2D, Len(B2D) -
1) & "E" & toGo
Jerry
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks