I did the math wrong! 1/(24*60*60*1000) is indeed 1.1574E-8
So we are getting closer to the IEEE precision bar than I thought
thanks
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email
Why the worry about Excel's precision? A millisecond is
only about 1.64 × 10^5 of a day.
A millisecond is about 1.1574E-08 of a day. To be more precise, it is
1/86400000 of a day. The denominator is 24*3600*1000.
You can confirm this by formatting a cell with the custom format
hh:mm:ss.000, entering 0:0:0.001, then reformatting the cell as
scientific with 4 or more dp.
I would have thought Excel could cope.
Or am I missing something?
A couple things. But before I get into details, let me say that it
does not seem to make much difference. When I multiply 0:0:0.001 by
86400000 (and format the cell as d:hh:mm:ss.000), I get
1:00:00:00.000, which Excel says equals 1.
What you are missing is: anytime we deal with decimal fractions that
cannot be represented exactly as a sum of a finite number (up to 53)
powers of 2, there is room for "numerical error". I'm sure you know
that.
Even in decimal, 1/24 is a repeating fraction, and so is 1/3600. So
you can imagine that 1/24/3600/1000 cannot be represented exactly with
a finite number of digits in decimal, much less binary.
In decimal, a millisecond is 1.15(740)...E-08 of a day, by which I
mean that 740 is repeating.
In 64-bit floating-point, 1/86400000 is represented by exactly
0.0000000115740740740740,738566885581896197254803126952538150362670421600341796875.
(The comma demarcates 15 significant digits to the left.) An
infinitesimal "error", to say the least. In fact, if we multiply that
by 86400000, we get exactly 1. The same is true if I use
1/24/3600/1000 and multiply by 24*3600*1000. (Note: Technically, the
order of those factors could make a difference. But I don't think it
does in this case.)
But 0:0:0.001 is represented by exactly
0.0000000115740740740740,755110497832956750752231300793937407433986663818359375.
Notice the difference after the comma, a slightly greater "numerical
error". When that is multiplied by 86400000, we get exactly
1.00000000000000,02220446049250313080847263336181640625. In an IF()
comparison, Excel considers that equal to 1. But the difference of
2.22E-16 might adversely impact some computations.
----- original posting -----