Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Why does Excel INT(x) behave like ROUND(x,0) for 10^14 and higher?
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Why does Excel INT(x) behave like ROUND(x,0) for 10^14 andhigher?
Errata (nitpick)....
On Jan 17, 10:53*pm, I wrote: 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Why does Excel INT(x) behave like ROUND(x,0) for 10^14 and higher?
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 "joeu2004" wrote: 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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Why does Excel INT(x) behave like ROUND(x,0) for 10^14 and hig
Errata: it was the original release of 2003 where RAND produced negative
numbers. Jerry "Jerry W. Lewis" wrote: 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/ ... |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Why does Excel INT(x) behave like ROUND(x,0) for 10^14 andhigher?
On Jan 18, 12:02*pm, Jerry W. Lewis
wrote: 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.... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Getting Excel MDI/SDI/Excel Instance to behave more logically | Setting up and Configuration of Excel | |||
Make Cell behave Like Word | Excel Discussion (Misc queries) | |||
How do I get filtering to behave consistently? | Excel Worksheet Functions | |||
Is there a way to count higher than 511 in binary in excel? | Excel Worksheet Functions | |||
How do I ROUND() round off decimals of a column dataset? | Excel Worksheet Functions |