Re: Serious bug with certain decimal numbers: Countifs and Roundup failed
wrote: interestingly this problem arises only if the integer part is above 1024. If I try 1023.89142, it seems to work very well
It is true that the formatting defect is evident with the decimal fraction 0.89142 only for numbers with integers 1024 to 65535. But the formatting defect is not limited to integer parts in that range. As I wrote previously, the formatting defect can arise with any integer part from 1 to 65535, and even when the integer part is zero if the fractional part is more than 0.5. For example: 0.5705043268391.461728482742.92409345954.5149137728.3362876116.5397627232.107219364.1050636128.1007522256.106409512.100477 But your original problem involved the formatting defect with date+times accurate to the second. I have determined that the formatting defect arises with the following:1. 128 of 86400 times (0.15%) for 3/19/1911 to 6/4/19222. 256 of 86400 times (0.30%) for 6/5/1922 to 11/7/19443. 640 of 86400 times (0.74%) for 11/8/1944 to 9/16/19894. 1280 of 86400 times (1.48%) for 9/17/1989 to 6/4/2079 The formatting defect does not arise for 3/18/1911 and earlier — again, for date+times accurate to the second. (I suspect the formatting defect arises more often with date+times accurate to the millisecond. But I have not played with such times. I feel like we are beating dead horse.) wrote: interestingly this problem arises only if the integer part is above 1024. If I try 1023.89142, it seems to work very well
It is true that the formatting defect is evident with the decimal fraction 0.89142 only for numbers with integers 1024 to 65535.
But the formatting defect is not limited to integer parts in that range.
As I wrote previously, the formatting defect can arise with any integer part from 1 to 65535, and even when the integer part is zero if the fractional part is more than 0.5.
For example:
0.570504326839
1.46172848274
2.9240934595
4.514913772
8.33628761
16.53976272
32.1072193
64.1050636
128.1007522
256.106409
512.100477
But your original problem involved the formatting defect with date+times accurate to the second.
I have determined that the formatting defect arises with the following:
 128 of 86400 times (0.15%) for 3/19/1911 to 6/4/1922

256 of 86400 times (0.30%) for 6/5/1922 to 11/7/1944

640 of 86400 times (0.74%) for 11/8/1944 to 9/16/1989

1280 of 86400 times (1.48%) for 9/17/1989 to 6/4/2079
The formatting defect does not arise for 3/18/1911 and earlier — again, for date+times accurate to the second.
(I suspect the formatting defect arises more often with date+times accurate to the millisecond. But I have not played with such times. I feel like we are beating dead horse.)