https://bz.apache.org/ooo/show_bug.cgi?id=126830
orcmid <[email protected]> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |[email protected] Latest|--- |4.1.2 Confirmation in| | Ever confirmed|0 |1 Status|UNCONFIRMED |CONFIRMED Component|ui |help --- Comment #2 from orcmid <[email protected]> --- RECOMMENDATION Realize that fractional days are carried as rational, approximate values and that approximation errors will occur on arithmetic with them, especially when whole values are expected when converted to integer seconds, minutes, hours, etc. I am treating this issue as Confirmed, but changing it to a documentation issue. We could do better than this, especially since it is very difficult for an user to see the source of the discrepancy. CORRECTIONS When integers are expected, mathematically, use the INT function to ensure that in the calculation. Changing the formula =cell*1440 to =INT(cell*1440) to ensure that an integer is produced works perfectly in the current case. SUPPLEMENTAL ANALYSIS In order to confirm exactly that the original problem is with floating-point accuracy limitation combined with rounding done in various places I did the following to the original Bug example. With formulas, such as B8 =B4, B9 = B5, etc., all across to column H, I then changed the values in rows 8 and 9 to be in Scientific notation, showing 16 digits to the right of the decimal point. However, this did not show any inaccuracy in the values of in column G although one sees the conversion limitations in columns B-F. The value in H9 is still 6E01 although H8 is not really 0, it is about 5.68434E-14 and displayed as 0 in H4. Instead of looking in the ODF code of the save .ods file, I came up with the following fix. I set I4 = FLOOR(H4;1). The formal definition of this result is the largest integer that does not exceed the given argument. Using the same for I5, I8, and I9, I get 0, 59, 0, and 59 as the four results. That is, the value in G5 is just slightly below 300, so mod(G5;60) is a value just below 60, but it rounds to 60 in H5 but its floor is 59 in I5. You can check for yourself that =FLOOR(60;1) is indeed 60. I agree this is not a bug, but recognition of the reason is exacerbated by roundings that occur in presenting results in ways where the difficult cannot be seen. In some sense, there is a defect, but how to address it remains a problem. I opened up Excel 2016 and created exactly the same two rows. In Excel, IH5 is also 60 and I5 is also 59. However, doing the same changes to scientific notation, H8 is revealed to be 5.9999999999999900E+01 although no discrepancy in 3.000...00E+02 is seen in the value of G8. While one could worry about this tiny discrepancy in the conversion for a cell output, and treat this as a confirmed defect. But it is not possible to eliminate these kinds of discrepancies. -- You are receiving this mail because: You are the assignee for the issue.
