295 views

Skip to first unread message

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

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

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.

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

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.

=0.09+0.01-0.1

returns zero,

=(0.09+0.01-0.1)

does not. See my other reply for an explanation.

Jerry

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:

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

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

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:

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

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

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

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

Is this correct?

> ' similar to scientific notation, 1.101B2 means

> ' 1*2^2 +1*2^1 +0*2^0 +1*2^-1 = 6.5

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

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

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

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

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")

Happy New Year! (but watch out for that "holiday cheer")

Jerry

Reply all

Reply to author

Forward

0 new messages

Search

Clear search

Close search

Google apps

Main menu