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