Description: The ROUNDDOWN function is imprecise in some cases involving decimal numbers. For example: ROUNDDOWN(8,94;2) yields 8,94 (correct). ROUNDDOWN(8,94-8;2) yields 0,93 (wrong). ROUNDDOWN(7,94-7;2) yields 0,94 (correct). Steps to Reproduce: 1.Let cell A1 =8.94-8 2.Note how the correct result is displayed (0.94). 3.Let cell B1 =ROUNDDOWN(A1;2) 4. Note the wrong result (0,93). Actual Results: Wrong result (0.93) Expected Results: Correct result (0.94) Reproducible: Always User Profile Reset: No OpenGL enabled: Yes Additional Info:
(In reply to Epo Nym from comment #0) > Description: > The ROUNDDOWN function is imprecise in some cases involving decimal numbers. i can reproduce this already with AOO 4.1.5. and result is different from MS Excel 2016: 0,94 adding two attachments...
Created attachment 150231 [details] demo roundown ods
Created attachment 150232 [details] demo_roundown xlsx
I understand the cause and in a way it is unavoidable. It is in most cases not possible with fractional numbers for the internal (binary) value to be exactly the same value as the (decimal) number. That means that the binary representation of 0.94 could be something like 0.939999999999999 (figures are an example, not actual values). Rounding down the latter will always produce 0.93. A similar problem will be visible with ROUNDUP for some values. I will see if I can find a way to avoid or at least significantly reduce the above phenomenon. As I don't know yet if that will be possible, I am not assigning the bug report to myself yet.
Winfried Donkers committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/+/edcbe8c4e02a67c74ec6f85f28899431dbfa0765%5E%21 tdf#124286 fix annoying rounding error. It will be available in 6.3.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
The patch fixes the issue for ROUNDDOWN as well as ROUNDUP (e.g. ROUNDUP(8.06-8;2).
issue 1: this bug isn't found by bugs search for 'rounddown' or 'ROUNDDOWN', thus i made a wrong statement in https://bugs.documentfoundation.org/show_bug.cgi?id=138220#c0 - sorry - and didn't take info from this into account for that bug, issue 2: '=8.94-8' displays '0,94' in calc, also with more decimals '0,94000000000000000000', but that is not! what calc uses for downstream calculations as you can see with '=RAWSUBTRACT(8,94-8;0,94)' -> '-4,44089209850063E-16', that need's to be fixed first, issue 2a: as well the imprecise calculation (@Mike Kaganski: PLEASE leave me alone with comments like this comment would be spam or fp-calculation-imprecision in such extend would be necessary or similar, no, it is a clear calculation error and can! be done better with correct handling of IEEE 754's capabilities, period) the result is 7 ULP off from the correct one ending in ~10100 instead of ~10000, that's a little too much for IEEE 754's strategy 'operands precise to +/- 0,5 ULP, results precise to +/- one ULP, thus fallback and auto correction to the nearest representation for the correct result', issue 2b: as well as hiding from the users what calc really does, showing '0,94' or worse: '0,94000000000000000000' instead of '0,93999999999999950000' is unfair and 'user trapping' (and wrong as deviating from 'weitz' (www.weitz.de/ieee) as far as weitz calculates correctly?), issue 2c: if?!? it is done - rounding for the display string mentioned in issue 2b which is! correct - the value! of the cell should be corrected as well, issue 2d: in this case it (the display deviating from the value) also trapped a dev and led him to work for 'better problems' :-( (https://gerrit.libreoffice.org/c/core/+/69762: 'This is caused by the i decimal to binary to decimal conversions.' no, at least this case is caused by not seeing the 'real value' and lack of appropriate handling of 'cancellation' occuring on subtraction of values with similar magnitude, and: binary to decimal conversion is safe, the error injected by decimal to binary representation should be less than +/-0,5 binary ULP, the deviation caused by 'cancellation' is 14 times that), issue 3: decorating various other calculations with inappropriate 'roundings' instead of fixing above error (issue 2a) undermines the reliability of calc, issue 4: the range of 8 <= x < 10 is one of the ranges that are particularly sensitive reg. overstretched expectations towards calc's / IEEE 754's capabilities of doing correct 16-decimal-digit-math, since the ratio 'decimal ULP for 16 digits' / binary ULP or 'available floats / 16-digit-decimals requiring representation' is very bad here with 0,562949953421312 (the 16th decimal digit is underdetermined) which leads to the fact that in this range sometimes the next binary float is more than one 16-digit-decimal ULP 'off' and thus values are changed and errors injected which become visible in the decimal result when using calc in excess of the IEEE 754 definition (15-dec-digit calculations), such errors do not occur or occur less frequently in many other ranges where sufficient floats are available to determine the 16th decimal digit unambiguously, issue 5: beforementioned situation (issue 4 only appearing in some ranges) often leads to the false assumption that IEEE 754 double floats are '16-decimal-digit-safe', they are not, imho the fix applied should be reverted or justified by other issues and this bug should be fixed by correcting the initiating calculation error, imho importance 'normal' or 'high' as correct rounding is elementar for plenty other calculations,
i'm doing something here that i know is not appreciated, i'm 'reopening' a resolved bug, please forgive me, it's just not resolved (for 8.94 minus 8 it is, but then it would have to be limited to that and it's not), the patch is ... ahem ... 'suboptimal'? why? because if you know how it works, you can quickly find counterexamples where it doesn't work, or even does harm: '=ROUNDDOWN(8,234567890125;11)' is screwed up just like '=ROUNDUP(8,234567890134;11)', and everything rounded up or down to 12 or more decimal places anyway ... '=ROUNDDOWN(123456789012345,5;2)' is another nice example ... or - simple theory and programming rules ... never do two step rounding or expect the hell of confusion ... but first i have to withdraw from my previous comment issue 2a, IEEE 754 is! (unfortunately!) insufficiently enough constructed to calculate 8.94 minus 8 to 0.93999999999995, it is responsibility of the compiler, the library, the programmer, the program, in this case of calc! how it handles occurring errors ('cancellation' in this case), '2.Note how the correct result is displayed (0.94)' - 2a. note how the wrong result is calculated ('=rawsubtract(A1;0,9)'), a simple rounding to two decimal places - before! further calculations! - would already help in this case, imho calc should do this itself and not pass the responsibility to the user, who can! not! take it (or only after years of painful complicated experiences) because calc does not even show him that it's calculating wrong ('decimal wrong') but rounds for the display 0.9399999999999995 to 0.94 ... grrrr ... yes this whitewashing! but unfortunately not! 'correcting' rounding even goes so far that the user can only see what is actually happening with tricks like rawsubtract! (even with 20 centimeters column width and 20 decimal places calc still shows '0,9400000000000000' ... grrrr ...) 'rounddown' calculates(s) correctly insofar, calc's handling of 'cancellation' is insufficient and should be improved, urgently!, AND THE DISPLAY! if one wants to improve rounddown 'with crutches' ... you are not supposed to! round two-step, but in this case (and all similar ones?) 'rounding to 15 significant decimal places' and then 'rounding down' (or up) wherever you want would perhaps be an allowed crutch ... (as the first rounding to 15 significant digits isn't a rounding but a correction measure to throw out fp-math artefacts) if then!!! 'roundsig' would work correctly ... which i doubt, at least to 15 digits because there often 10^-15 with its large deviation plays into it, and correcting that suffers from difficulties because somewhere else someone has injected a 4-bit truncation ... gradually this becomes a downward spiral of pain ... pls. pull this patch out, if someone would check and eventually correct roundsig i can try to change this patch from 12 to 15 digits rondsig as an exercise if i can also 'program' and not only complain ... (partly Translated with www.DeepL.com/Translator (free version), thus may contain errors in wording, syntax, values or other)
think @zcrhonek did a bibisect in https://bugs.documentfoundation.org/show_bug.cgi?id=138220#c2
B., thanks for following up, at the same time you already have bug 138220 open on the problem you encountered, please leave this bug report closed.
@Aron Budea: then it would be right to close it as duplicate, otherwise people might think roundup / rounddown problems are resolved, OTOH closing a bug as duplicate of another bug which complains about insufficiencies injected by the closed bug is ... funny? i reopened this bug - after extensive consideration - because it's original issue isn't resolved (only changed from plenty small occurences to fewer? more harmful occurences), it is NOT resolved, as i wrote: '=rounddown(8.94-8;2)' -> 0.93 is resolved, but the subject of the bug: 'ROUNDDOWN is inaccurate with decimal numbers' isn't. suggestion: reopen this bug and set 138220 duplicate. (i dislike how much 'bugs' gets used to administering bugs and duplicates instead of solving them, and to 'defend' old wrong decisions, more and more every day)
(In reply to b. from comment #11) > @Aron Budea: then it would be right to close it as duplicate, otherwise > people might think roundup / rounddown problems are resolved, These are bug reports for specific issues, not reports on feature completeness, the more noise one has the harder it is for someone to follow up on it. > i reopened this bug - after extensive consideration - because it's original > issue isn't resolved (only changed from plenty small occurences to fewer? > more harmful occurences), > it is NOT resolved, > as i wrote: '=rounddown(8.94-8;2)' -> 0.93 is resolved, but the subject of > the bug: 'ROUNDDOWN is inaccurate with decimal numbers' isn't. The title is just a title, the description describes the problem. If there are issues with the fix that isn't related to the originally reported problem, it especially belongs to a different bug report.
It's ok in Version: 7.1.3.2 (x64) / LibreOffice Community Build ID: 47f78053abe362b9384784d31a6e56f8511eb1c1 CPU threads: 4; OS: Windows 10.0 Build 19042; UI render: Skia/Raster; VCL: win Locale: ro-RO (ro_RO); UI: en-US Calc: threaded
as long as: '=ROUNDDOWN(2-5E-015)' still results in 2, despite the difference to two is clearly calculateable: '=RAWSUBTRACT(2-5E-015;2)' -> -5,10702591327572E-15 and similar miscalculations it's not ok for me ... rounding is a knife to make sharp mathematical decisions, and those should be as exact as possible ... not 'by chance',
Unfortunately no one can tell whether the value 1.999999999999995 was meant to be an exact value or is the result of a calculation with slight imprecision due to not exactly representable decimal values. Hence 1.999999999999995 is rounded to 14 decimals resulting in 2 before floor() is taken. If we did not do that there'd be other calculations where the result would be off by one but much worse than this constructed case. You're welcome to find a sharper implementation for rtl_math_approxValue() at https://opengrok.libreoffice.org/xref/core/sal/rtl/math.cxx?r=ecfcd99a#1287 Apart from that, if you want to make sharp mathematical decisions based on the 16th significant decimal digit then do not use a spreadsheet software that calculates using IEEE 754 binary double floating-point values that can only represent 14 to 16 digits, and also tries catering to inexperienced users pampering them with values that otherwise would be impossible to reach.
hello @erAck, > You're welcome to find a sharper implementation for rtl_math_approxValue() at https://opengrok.libreoffice.org/xref/core/sal/rtl/math.cxx?r=ecfcd99a#1287 tried to patch it at another point, https://bugs.documentfoundation.org/show_bug.cgi?id=138220#c7, IMHO not the! final solution but a step in the right direction, would you mind to look at / care for?
hello @erAck, > Unfortunately no one can tell whether the value 1.999999999999995 was meant to be an exact value or is the result of a calculation with slight imprecision due to not exactly representable decimal values. short for you: but calc can and does correctly (IMHO) tell / decide that for e.g. '=ROUNDDOWN(2-5E-015;14)' -> 1,99999999999999 and '=ROUNDDOWN(2-5E-015;13)' -> 1,9999999999999 prose for others: even '=ROUNDDOWN(2-1E-015;14)' -> 1,99999999999999 and '=ROUNDDOWN(2-1E-015;13)' -> 1,9999999999999 get correct results, as well as '=ROUNDUP(2+1E-015;14)' -> 2,00000000000001 and '=ROUNDUP(2+1E-015;13)' -> 2,0000000000001 thus throwing away some bits and producing incorrect results (a rounddown should never be bigger than the original value as a roundup should never be less) is therefore an idiosyncratic decision to assume that users who want to round to less than 13 decimal places are more likely to have miscalculated a result and should therefore be deprived of the possible precision at the cost of possibly damaging their results, whereas users who want to round to 13 or 14 decimal places are immune to calc's calculation errors and can be left alone with wrong results? In my opinion this is not a good 'UI' as it: 1. is inconsistent, 2. is mathematically wrong, 3. is nowhere documented or communicated to the user (i miss a banner: 'Attention!, rounding to less than 13 decimal places may give you mathematically wrong results while rounding to 13 or 14 decimal places will keep calculation errors from calc, effect may vary with size ranges as within the internal calculation the overall precision consisting of integer and fractional part is pruned' or similar), 4. blocks meaningful efforts to achieve real precision with rounding, maybe it was once useful or less noticeable when 16 bit was used and all rounding routines in calc were error-prone anyway? nowadays i would say: 'who announces IEEE 754 "double" precision to the user should also deliver it'.
(In reply to b. from comment #16) > tried to patch it at another point, > https://bugs.documentfoundation.org/show_bug.cgi?id=138220#c7, IMHO not the! > final solution but a step in the right direction, would you mind to look at > / care for? It fails, see https://bugs.documentfoundation.org/show_bug.cgi?id=138220#c8
5e-15 nearest representable is 0.0000000000000049999999999999999940965467727994934856716453645819608908595910179428756237030029296875 2 - 5E-015 -> 1.9999999999999949 (14 digits 9 and 49) (1.9999999999999948929740867242799140512943267822265625) =ROUNDDOWN(2-5E-015) 1.9999999999999949 approxValue() -> 2 =ROUNDDOWN(2-5E-015;14) 1.9999999999999949 scaled by 100000000000000 (1e14) -> 199999999999999.5 approxValue() -> 199999999999999.5 floor() -> 199999999999999 scaled back by 100000000000000 (1e14) -> 1.99999999999999 (1.9999999999999900079927783735911361873149871826171875) display rounded to 1.99999999999999 =ROUNDDOWN(2-5E-015;13) 1.9999999999999949 scaled by 10000000000000 (1e13) -> 19999999999999.949 (19999999999999.94921875) approxValue() -> 19999999999999.949 floor() -> 19999999999999 scaled back by 10000000000000 (1e13) -> 1.9999999999999001 (1.999999999999900079927783735911361873149871826171875) display rounded to 1.9999999999999 The rest of your prose ist just assumptions and wrong speculations.
hello @erAck, 'The rest of your prose ist just assumptions and wrong speculations.' - :-( 'You're welcome to find a sharper implementation for rtl_math_approxValue() at https://opengrok.libreoffice.org/xref/core/sal/rtl/math.cxx?r=ecfcd99a#1287' - did so, think it works, but have to pull out the handling of cancellation errors by 'relaxed rounding' ('=rounddown(8,94 - 8; 2' ..., and the respective tests in rounddown.fods and roundup.fods). would like to replace the cancellation handling and re-activate the QA-tests. have some primitive code ready which 'cancels cancellation' - by rounding to appropriate amount of decimals, calc doe's a quite good job once all that 'unspecific pumpgun rounding' is out). but placing it in 'approxSub' in math.hxx leads to a chain of 'not declared' and 'no function definition here' and so on, as said, i'm neither a programmer nor a coder ... have a proof 'in sheet' that it works to good degree with a 'precision-patched' version of calc. need a little help in structuring and / or code pointers: I: some additions are subtractions ('= 3 + (-5)', '= -1234,12 + 1234'), and some subtractions are additions ('= 5 - (-7)', '= -123,44 - 22,34)'), 'different sign of operands changes the operation'?. imho it would be meaningful to divide and recombine them for the calculation of the value, and then re-combine that with a calculation of the appropriate sign, in a way that there is one part in code handling all 'real additions' (needing treatment against roundoff/-on deviations (that will come later because it needs cancellation free subtractions first)). and another part handling all 'real subtractions' (needing treatment against cancellation, i can provide that but it requires accurate rounding). what would be best way / place to implement such, or is it already in? (i think i have seen something in that direction in interpr5? in context of matrix functions? 'CalculateAddSub' or similar). II: above is too much work for me, especially as i'm not familiar what to calculate where, and as it's a slightly complex handling of 16? cases having influence on the sign for the final result. and needs plenty of knowledge where to place to catch all similar calculations. can you give me a code pointer or build a structure where i have two places, one for subtraction of two positive operands, two doubles in and one double out (or a second one with the error if such can play together with the kahaning from @Dante). and one for addition of such, same structure, two doubles in and 1 (+1?) out. in a way that i can place my corrective calculations there and have access to the usual math operations like min, mod e.t.c. giving out one result, negative in case of subtrahend > minuend?, and the handling of the signs and re-stuffing the results in the workflow is done before and after the quantitative calculation? would be very 'nice', and help calc to gain some of the claimed IEEE precision. as @erAck normally is quite busy help from other developers is welcome as well ...
see steps: 1. pull out roundsig(x,12) from rounddown/roundup, 2. refine 'approxValue', 3. resharpen rounding, 4. fight cancellation errors by selective rounding, working quite far, and questions who'd like to help and how to proceed in: https://ask.libreoffice.org/en/question/312803/code-pointer-refining-subtractions/
Please submit your code change to gerrit so it can be reviewed and tested. See https://wiki.documentfoundation.org/Development/gerrit for how to.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/8186a01f2a26f05645a2a3c9c93b453bd35b796f Resolves: tdf#138220 tdf#154792 Avoid double rounding; tdf#124286 follow-up It will be available in 7.6.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/e68cd91651c53c5c228d84582b1062d8fb9a7077 Add ROUND(DOWN|UP) samples to unit test, tdf#154792 tdf#124286 tdf#138220 It will be available in 7.6.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Eike Rathke committed a patch related to this issue. It has been pushed to "libreoffice-7-5": https://git.libreoffice.org/core/commit/f0f2ceaf978a0eee8e3c1fcfe6dec546ab8f07d6 Resolves: tdf#138220 tdf#154792 Avoid double rounding; tdf#124286 follow-up It will be available in 7.5.4. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Forgive me for offering a wildly crazy suggestion from the side line. Just as a FYI, in some formal accounting applications, fractions are internally stored and processed as divisions: 0.5 -> 1/2. This is to keep calculations as long as possible correct. Maybe this might work for Calc too.