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

295 views
Skip to first unread message

Valdi

unread,
Aug 16, 2005, 4:44:24 AM8/16/05
to

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?action=getinfo&userid=26338
View this thread: http://www.excelforum.com/showthread.php?threadid=396049

Duke Carey

unread,
Aug 16, 2005, 7:51:03 AM8/16/05
to
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

Jerry W. Lewis

unread,
Aug 16, 2005, 8:01:53 AM8/16/05
to
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/78113?#XSLTH3196121122120121120120
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

Jerry W. Lewis

unread,
Aug 16, 2005, 8:05:41 AM8/16/05
to
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

GeorgeB

unread,
Aug 16, 2005, 11:38:22 AM8/16/05
to
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?

Jerry W. Lewis

unread,
Aug 16, 2005, 10:56:12 PM8/16/05
to
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

GeorgeB

unread,
Aug 17, 2005, 6:27:05 AM8/17/05
to
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/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.

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.

Jerry W. Lewis

unread,
Aug 18, 2005, 7:24:43 AM8/18/05
to
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

unread,
Jan 1, 2006, 10:36:55 PM1/1/06
to
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

Bernard Liengme

unread,
Jan 2, 2006, 9:15:11 AM1/2/06
to
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

unread,
Jan 2, 2006, 6:47:03 PM1/2/06
to
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

unread,
Jan 3, 2006, 8:05:28 AM1/3/06
to
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" <Jerry...@discussions.microsoft.com> wrote in message
news:8A1E6C42-EA55-4208...@microsoft.com...

Jerry W. Lewis

unread,
Jan 3, 2006, 1:30:03 PM1/3/06
to
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

Reply all
Reply to author
Forward
0 new messages