It's sometimes interesting to try to work out exactly what chain of
calculations Excel is performing under the covers, but I think (without
going into the gory details) that Excel is working as designed in your
example.

Most real-world spreadsheets contain a chain of calculations where the
effect of the floating-point calculations makes an absolute comparison of
the low-order bits such as you are attempting a pointless exercise.

As you have demonstrated, if you need such accuracy in the low-order bits
you should not be using Excel.

regards
Charles
___________________________________
The Excel Calculation Site
http://www.decisionmodels.com

"JoeU2004" wrote in message
...
PS....

"Charles Williams" wrote:
This approximates to 15 significant decimal digits, as
you have found. This is the way it is designed to work

I responded:
Sorry, but you are wrong.

... Not that I would object to a calculation option that would do just
that, namely: force the result of all formulas to be rounded to 15
significant digits, not unlike the "Precision as displayed" option, but
more generally applied.

That would legitimatize some of the heuristics that Excel has implemented
to try to ameliorate the aberrations due to binary floating point
arithmetic. For example, as I noted in response to Jerry elsewhere in this
thread, the presumptive behavior of INT would work in my specific
example -- A1-INT(A1) -- if the formula in A1 had been rounded to 15
significant digits.

I imagine the performance cost would be equivalent to the performance cost
of "Precision as displayed".

Arguably, there would be still an issue with subexpressions in formulas.
For example, 123456789 - 0.0000004 - INT(123456789 - 0.0000004) might
still be a problem, at least not without an expensive solution.

Moreover, it would not mask the effects of all aberrations caused by
binary floating point arithmetic. But it should eliminate the "hidden"
effects, effects that cannot be seen even when formatting the cell to
display 15 significant digits.

In any case, Jerry's comments do suggest a work-around to the specific
anomaly that I presented in the original posting, to wit:
VALUE(A1&"")-INT(A1) is well-behaved, given the presumptive behavior of
INT.


----- original message -----

"JoeU2004" wrote in message
...
"Charles Williams" wrote in message
...
All numbers in Excel are IEEE Binary floating point.

Of course. Whadaya think I meant when I wrote, "You need to look at the
exact conversion of the internal binary representation -- that is, beyond
the first 15 significant digits"?

And wheredaya think I'm getting all those extra digits when I wrote that
123456789 - 0.0000004 is "about 123456788.999999,598"?

(It is exactly 123456788.999999,59766864776611328125. Or if you prefer
binary, &h419D6F34,53FFFFE5, which is a stylistic way of writing
0x419D6F3453FFFFE5 in C.)


This approximates to 15 significant decimal digits, as
you have found. This is the way it is designed to work

Sorry, but you are wrong. Reason it out for yourself.

If you were right, how could I subtract 0.0000004 from 123456789 in the
first place? And how could subtracting 0.0000005 have different results?

(Hint: Reread my original posting. I answer those questions.)

And if A1 is positive, A1-INT(A1) should never return a negative number.
Zero, maybe; but not negative.

Finally, for your edification, try the following experiment.

A1: 12345789
A2: =A1 + 2^-26
A3: =A1-A2=0

Format A1 and A2 to 15 significant digits (e.g. Scientific with 14 dp).
Note that A1 and A2 __appear__ to be the same. But A3 being FALSE proves
they are not.

Even though Excel formatting is limited to 15 significant digits (as is
data entry), arithmetic is performed to the full precision of 64-bit
floating point, which is more than 15 significant digits.

(Actually, pairwise operations are performed to the precision of 80-bit
floating point, then rounded to 64-bit floating point.)


as outlined in Excel Help etc.

I'm afraid that MS tech writers tend to over-simplify technical
explanations, and they often get it totally wrong.

For example, http://support.microsoft.com/kb/78113 states: "although
Excel
can store numbers from 1.79769313486232E308 to 2.2250738585072E-308, it
can
only do so within 15 digits of precision".

That is flatly incorrect, as it relates to results from arithmetic
operations, as I demonstrate above.

(And arguably, if the sentence above were intended to refer to storing
constants, not results of calculations, the tech writer is still wrong
because constants have a more limited range, at least in Excel 2003.)


----- original message -----

"Charles Williams" wrote in message
...
All numbers in Excel are IEEE Binary floating point.
This approximates to 15 significant decimal digits, as you have found.

This is the way it is designed to work, and is as outlined in Excel Help
etc.

Charles
___________________________________
The Excel Calculation Site
http://www.decisionmodels.com

"JoeU2004" wrote in message
...
Would someone who has Excel 2007 running on an Intel-compatible
computer
(i.e. not a Mac) please try the examples below and post your results
here?

I posted this inquiry to an Excel 2010 blog. Not sure if/when I'll
hear
back from them.

(Extra credit: If you have Excel 2010 running on an Intel-compatible
computer, feel to try these examples and post your results here, too.)


Has the following Excel 2003 problem been fixed in 2010 (or 2007)?

INT(123456789 - 0.0000004) returns 123456789 instead of 123456788.

This causes a problem in formulas like the following: if A1 is
=123456789-0.0000004, =A1-INT(A1) is negative unexpectedly,
about -4.917E-07 when formatted as General.

In contrast, INT(123456789 - 0.0000005) returns 123456788 as expected,
and myInt(123456789 - 0.0000004) returns 123456788, where myInt() is
the
following UDF:

Function myInt(x as Double) as Double
myInt = Int(x)
End Function

Note that 123456789 - 0.0000004 is represented internally as about
123456788.999999,598, whereas 123456789 - 0.0000005 is about
123456788.999999,493 internally. (The comma demarcates the first 15
significant digits.)

So I suspect that the Excel INT algorithm is effectively, albeit
perhaps
unintentionally, rounding its argument to 15 significant digits before
truncating to an integer. It shouldn't.

Indeed, the largest expression involving 123456789 that returns an
incorrect INT value is 123456789 - 33*2^-26, which is represented
internally as about 123456788.999999,508, whereas 123456789 - 34*2^-26
is
about 123456789.999999,493 internally.

As you might imagine, the problem is not limited to 123456789 -
0.0000004. And the problem will not appear with some combinations that
you might think are similar, e.g. 100000000 - 0.0000004.

You need to look at the exact conversion of the internal binary
representation -- that is, beyond the first 15 significant digits -- to
determine whether or not to expect a problem. Most people cannot; I
can.