Why does Excel INT(x) behave like ROUND(x,0) for 10^14 and higher?

[joeu2004]

INT(10^14+0.5) results in exactly 100000000000001. INT(10^14-1+0.5)
results in exactly 99999999999999, as it should.

It seems that for all values of 10^14 and higher [1], INT() behaves
like ROUND(,0). Why is that?

I think this has been discussed before. But I cannot find the
previous discussion(s) or any MS KB on the subject. Pointers would be
appreciated, as well as an explanation.

I don't think this has to do with IEEE 64-bit double floating-point
representation. VBA Int() works fine in all cases. I also don't
think the problem has to do with Excel 64-bit arithmetic v. VBA 80-bit
arithmetic. At least, any such explanation is not obvious to me,
having looked at the binary representation. I also tried storing
intermediate subexpression results into type Double variables, with no
adverse effect.

IEEE 64-bit double floating-point can represent integral numbers up to
2^53 accurately. 10^14 requires only 46 of the 52 mantissa bits.

On the other hand, I suspect it is no accident that 10^14 is 15
significant digits, the Excel display limit. Does Excel implement
INT() by converting the argument to text, then trimming the digits to
the right of the decimal point? (Surprise!)



End Notes:

[1] Obviously, I have not looked at all integers above and below
10^14. I found 10^14 by a binary search between 1 and 13^14. A more
accurate statement is: INT() works fine for 20 values in the range
between 1 and 10^14-1, and INT() mishaves for 31 values between 10^14
and 13^14. I ass-u-me continuity because the binary search worked as
expected.

 

[joeu2004]

Errata (nitpick)....

10^14 requires only 46 of the 52 mantissa bits.

I meant to say: numbers around 10^14 require only 46 of the 52
mantissa bits;
specifically, 2^46+1 (70368744177665; about 0.7E+14) through 2^47-1
(140737488355327; about 1.4E+14). 10^14 itself requires only 32
bits. Not an important point.

 

[Jerry W. Lewis]

Interesting! Since neither VBA nor worksheet MOD functions work over the
full range of numbers, I wonder if this is how the new (in 2007) RAND()
function incorrectly produced negative numbers in the original release?
http://support.microsoft.com/kb/834520/

The worksheet functions ROUNDDOWN, TRUNC, and FLOOR behave exactly the same
way as INT. VBA function FIX as well as Int still works correctly in this
range.

WAG as to what MS was thinking: If you ask Excel to round a number, you
probably will evaluate its performance based on what you can see (documented
15 digit limit). Why they extend that thinking to INT, TRUNC, etc is a
mystery.

You are correct that this is not a binary representation issue, since are 5
trailing zero bits after the .5.

With Excel 97, began aggressively intruding into the arithmetic in an
ill-advised (IMHO) attempt to try to hide the binary nature of its numerics
http://support.microsoft.com/kb/78113
VBA was never handicapped in this way. It would be interesting to know how
these worksheet functions behaved prior to Excel 97.

Also a mystery is why =1E14+0.5 displays as 100000000000000 instead of
100000000000001 in Excel 2003 (what about 2007 or pre-97 versions?). This
may be another one of the hundreds of thousands of decimal fractions that
Excel does not display correctly (off by one at the 15 decimal place),
contrary to
http://support.microsoft.com/kb/161234
that only admits the problem for the one decimal fraction .848

Jerry

INT(10^14+0.5) results in exactly 100000000000001. INT(10^14-1+0.5)
results in exactly 99999999999999, as it should.

It seems that for all values of 10^14 and higher [1], INT() behaves
like ROUND(,0). Why is that?

I think this has been discussed before. But I cannot find the
previous discussion(s) or any MS KB on the subject. Pointers would be
appreciated, as well as an explanation.

I don't think this has to do with IEEE 64-bit double floating-point
representation. VBA Int() works fine in all cases. I also don't
think the problem has to do with Excel 64-bit arithmetic v. VBA 80-bit
arithmetic. At least, any such explanation is not obvious to me,
having looked at the binary representation. I also tried storing
intermediate subexpression results into type Double variables, with no
adverse effect.

IEEE 64-bit double floating-point can represent integral numbers up to
2^53 accurately. 10^14 requires only 46 of the 52 mantissa bits.

On the other hand, I suspect it is no accident that 10^14 is 15
significant digits, the Excel display limit. Does Excel implement
INT() by converting the argument to text, then trimming the digits to
the right of the decimal point? (Surprise!)



End Notes:

[1] Obviously, I have not looked at all integers above and below
10^14. I found 10^14 by a binary search between 1 and 13^14. A more
accurate statement is: INT() works fine for 20 values in the range
between 1 and 10^14-1, and INT() mishaves for 31 values between 10^14
and 13^14. I ass-u-me continuity because the binary search worked as
expected.

 

[Jerry W. Lewis]

Errata: it was the original release of 2003 where RAND produced negative
numbers.

Jerry

 

[joeu2004]

WAG as to what MS was thinking: If you ask Excel to round a number, you
probably will evaluate its [behavior] based on what you can see (documented
15 digit limit).  Why they extend that thinking to INT, TRUNC, etc is a
mystery.

And even more of a mystery since, as you say, 1E+14+0.5 is not even
displayed as 100000000000001 (if I counted the zeros correctly <g>) in
Excel 2003, when formatted as Number.

BTW, I should have noted that I am, indeed, using (Office) Excel 2003.

Anyway, thanks for your comments. I was hoping that you, especially,
would have an answer for me. Oh well....