Description: looking for some accuracy problems i spotted that calc's 'round' fails for plenty values, as similar happened with a user defined round routine i investigated further and came to the point that: '=7000000000000000*0,00001/0,00001' fails (6999~), while '=7000000000000000/100000*100000' holds, acc. school math the results should be equal, 'weitz' tells me 70000000000 / 0,00001 = 6,9999~, question 1: weitz and calc may share some basic problem - buggy library, wrong compiler options or similar, question 2: if the error is 'unavoidable' would it be a good idea to use the better of both formulas in calc? i 'assume' calcs rounding routine uses the 'wrong' of both formulas and thus fails sometimes ... (my 'user space' rounding routine works fine since i exchanged that part, before it had similar errors as calc), question 3: may it be possible that this or similar errors, which can be easily circumvented in the same or a similar way, are the cause of many other 'inaccuracies' in calc? (fp-math isn't 'decimal-safe' as well as decimal math isn't 'real-world-safe', but avoidable problems should be avoided and not sorted out as 'fp-problem, it has to be that way', earlier versions of calc were 'better', so i think that 'better' is possible, didn't do extensive testing, but on a first glance even ex$el looks better, and i think it is important, such elementary errors affect many other calculations ... Steps to Reproduce: 1. key '=ROUND(5e15+1;9)' in a cell, 2. hit enter, 3. observe result 5000000000000002, 4. try to explain this to a user with simple math knowledge 5. key '=70000000000/0,00001' in a cell, 6. observe result 6999999999999999, 7. if you want to say 'notabug, this is just fp-maths, learn first why it has to be that way': 8. try the same steps in calc ver. 4.1.6.2, it is not! unavoidable, 9. call it a regression, Actual Results: 5000000000000002, 6999999999999999 Expected Results: 5000000000000001, 7000000000000000 Reproducible: Always User Profile Reset: Yes Additional Info: Version: 7.1.0.0.alpha1+ (x64) Build ID: b61bf7c7cfcf97a5ade6d130873af146670bc2ee CPU threads: 8; OS: Windows 6.1 Service Pack 1 Build 7601; UI render: default; VCL: win Locale: de-DE (de_DE); UI: en-US Calc:
(In reply to b. from comment #0) > '=7000000000000000*0,00001/0,00001' fails (6999~), while > '=7000000000000000/100000*100000' holds, > > acc. school math the results should be equal, 'weitz' tells me 70000000000 / > 0,00001 = 6,9999~, You can't apply school math on IEEE-754 binary floating-point values over all ranges. Period. 0.00001 has no exact binary representation, hence 70000000000 / 0.00001 => 6999999999999999 > question 1: weitz and calc may share some basic problem - buggy library, > wrong compiler options or similar, They share IEEE-754 binary floating-point. > question 2: if the error is 'unavoidable' would it be a good idea to use the > better of both formulas in calc? i 'assume' calcs rounding routine uses the > 'wrong' of both formulas and thus fails sometimes ... There's no extra rounding involved in multiplication and division. Also, the formula expression is calculated as entered from left to right, there's no "use the better". > question 3: may it be possible that this or similar errors, which can be > easily circumvented in the same or a similar way, are the cause of many > other 'inaccuracies' in calc? There's no Calc error in this case. And this case of inaccuracy can't be circumvented unless the final value is rounded but to what criteria? > earlier versions of calc were 'better', so i think that 'better' is > possible, Which version yielded the result 7000000000000000 for this very example? I tried 5.3.7 and it delivers the same 6999999999999999 result. Also doing that math in a raw C++ compiled program yields 6999999999999999. Also Gnumeric agrees. > didn't do extensive testing, but on a first glance even ex$el looks better, Maybe they apply some magic. Ask them to reveal their tricks. > and i think it is important, such elementary errors affect many other > calculations ... Yes, IEEE-754 binary floating-point is not perfect. Get over it. > Steps to Reproduce: > 1. key '=ROUND(5e15+1;9)' in a cell, > 2. hit enter, > 3. observe result 5000000000000002, I'll keep this bug for the rounding thing, we maybe could do better to not round integer values beyond the decimal. Apart from that Gnumeric also delivers the same 5000000000000002 result. > 8. try the same steps in calc ver. 4.1.6.2, it is not! unavoidable, Oh, that version does it different? Maybe it did other things wrong. It delivered better results for both 5000000000000002 6999999999999999 ? > 9. call it a regression, If so, maybe a bibisect could shed some light. Requesting. But it could also be that compilers' IEEE-754 handling or libraries changed somewhat. And that even both on Windows and Linux.
hello @erAck, generally speaking: i agree that 'fixing floating point math' is a tough problem, but there has been progress, and i hope more is possible, a (workaround) solution/improvement (at least for the rounding problems) could be to store predefined values for the three 'painpoints' (10^-5, 10^-9, 10^-15) (respectively their reciprocal values 1/1E-5, 1/1E-9, 1/1E-15), and use them instead of 'on the fly' wrong calculated values to avoid the evil influence they actually have (if / as long as there are no better ways?!?), --- but who wants 'better problems' ;-) as far as i was able to check with calc as it calculates just now there are only these three values for which an error becomes visible with '=RAWSUBTRACT(1/[X];ROUND(1/[X];0))', absolute it is greatest with -0,125 for 1E-15, relative ('=RAWSUBTRACT(1/[X];ROUND(1/[X];0))/ROUND(1/[X];0)') it is greatest for 1E-5 with (-1,45519152283669E-16), for all other arguments from 1E-1 to 1E-308 the results is '0', if you look at other enumerators than '1' (i did for the powers 10^0 to 10^100) 1E9/1E-5 has the max relative deviation of -1,5625E-16, absolute -0,015625, and an 'uneven' value near it is 955037695 with a relative deviation of -1,63606107714942E-16, absolute -0,015625, while an ULP in that range is 0,010603048383739, thus a claim "result of a fp calculation shouldn't be more than 1 ULP off from 'standard math'" (0,5 ULP from each argument) doesn't hold ... (this claim isn't valid for all operations, i know), i haven't checked it extensively yet, but i think as long as theese three painpoint values are allowed to inject deviations larger than one ULP into the calculations (which are visible with rawsubtract), wrong results will appear from time to time and bring up complaints, imho this proposal is a 'crutch', but not completely stupid, in other areas such things are used as best practice, and now forgive my cumbersome thinking and persistent arguing, progress would be slower if not again and again someone would try, and i just have to work my way into the problems first ... and forgive my long writing and the repetitions below, i'm just limited in time to make it short and concise :-( > You can't apply school math on IEEE-754 binary floating-point values over all ranges. Period. accepted, but imho all efforts to get as much as possible are 'good', and there have been improvements over the years, thus progress isn't impossible, > 0.00001 has no exact binary representation, hence yes, if i'm right its the one value of 1E-1 ... 1E-308 neccessary for rounding to decimals which is most 'off' from what it should be, > 70000000000 / 0.00001 => 6999999999999999 yes, see 'better' below, > They share IEEE-754 binary floating-point. but there are variations how you can use it? options? or might even be flaws in it? > There's no extra rounding involved in multiplication and division. Also, the formula expression is calculated as entered from left to right, there's no "use the better". my idea was that the programmer / developer has at hand how a rounding is calculated behind the scenes, and if he - not the user in the sheet who isn't aware of such problems - would steer '=round(7000000000000000;-5)' to calculate: '=7000000000000000/100000 (+/- [0..0,9] to steer the rounding) 'take int' *100000', instead of: '=7000000000000000*0,00001 (+/- [0..0,9] to steer the rounding) 'take int' /0,00001', calc would gain better results, couldn't test yet if it holds for all cases as tie to even and '4-bit cut' stepped in, > There's no Calc error in this case. And this case of inaccuracy can't be circumvented unless the final value is rounded but to what criteria? i'm thinking about such problems and testing in this direction, at the moment stuck by - see above -, - at least for some cases fixing the arguments could be better than rounding the results, e.g. > 70000000000 / 0.00001 => 6999999999999999 '=70000000000 * (1/0,00001)' => 6999999999999999 '=70000000000 * ROUND((1/0,00001);0) => 7000000000000000 - for many operations one can calculate arguments for a meaningful rounding from the operands, unfortunately not for divisions?, if you calculate a rounded value by '=7000000000000000*0,00001 ~ ~ *100000' then you can legitimately round to -15 decimal places (-15 from 7000.., +5 from 0,00001, -5 from *100000), - if you calculate 70000000000 * 100000 instead of 70000000000 / 0.00001 no error will occur, but if you can legitimately round to '-15' decimal places, > Which version yielded the result 7000000000000000 for this very example? I tried 5.3.7 and it delivers the same 6999999999999999 result. Version: 4.1.6.2 Build ID: 40ff705089295be5be0aae9b15123f687c05b0a, was astonished myself as it was 'better than Weitz' ... did a recheck, even on neighboured values, no error for '/0,00001' in the range '70000000000 - 35' to '70000000000 + 36', not 'error outside that range' but 'outside that range not yet tested', > Also doing that math in a raw C++ compiled program yields 6999999999999999. Also Gnumeric agrees. i suspect a mistake or 'something special' at my side, but what? and of course you can't 'embarrass' LO or calc if / as long as basic functionalities like IEEE or C-compiler have bugs or 'limitations', but we can / should insist that these basic things are improved (it can, should and mustn't be that 50 years after a computer controlled flight to the moon personal computers do worse calculations than simple pocket calculators for 1,50 EUR), and calc or LO can - and should - try to work around the weak points as far as they are known, if i spotted a weakness in IEEE, c-compilers, gnumeric or even intel FPU it schould be corrected there, what i dislike is 'fp-math has to have fails' and no further care ... if there are unavoidable errors we need to learn to live with it, >> didn't do extensive testing, but on a first glance even ex$el looks better, > Maybe they apply some magic. Ask them to reveal their tricks. my connection to Micro$oft is a bit weak and burdened with a lot of trouble, maybe better someone else could ask ... is that magic? instead of '=80000000000 / 0,00001' calculate '=80000000000 * round(1/0,00001;x)' with values for 'x' from 0 to -4, !!! don't try 'round(1/0,00001;-5)' ... or do!, it is funny, did it in discrete steps in separate cells, had a minute to think why things obviously looking right!!! failed, then remembered 'rounding to -5 fails in calc', 'even 100000,00000000000000000000 shown as result for '=round(1/1E-5;-5)' may deviate from 100000', it doe's!, i'm still smiling about my silly attempt ... ;-) !!! > Yes, IEEE-754 binary floating-point is not perfect. Get over it. i proposed IEEE-754 2008, @Mike Kaganski: 'NO!', i'm trying user space macros, not so bad but slow, still have fun to work on it ... i'm in hope that i can produce ideas which - if coded - will enhance calc, > I'll keep this bug for the rounding thing, we maybe could do better to not round integer values beyond the decimal. that would avoid some problems, but solving the /1E-5 issue would be better as it also heals other calculations where it's not so easy to spot deviations, > Apart from that Gnumeric also delivers the same 5000000000000002 result. school math and user expectations won't change because Gnumeric and / or ex$el fail, '=((5000000000000000+1)*1000000000+0,5)/1000000000' -> 500000000000000**2**, '=((5000000000000000+1)/0,000000001+0,5)*0,000000001' -> 500000000000000**1**, not a solution for all cases, but a proof that theese cases can be calculated correct, >> 8. try the same steps in calc ver. 4.1.6.2, it is not! unavoidable, > Oh, that version does it different? Maybe it did other things wrong. yes, of course, maybe someone can try to combine the strong points of both versions? > It delivered better results for both 5000000000000002 6999999999999999 'm not fully satisfied with 4.1's '5000000000000000' for '=ROUND(5e15+1;9)', but again, wrong results are not carved in stone, >> 9. call it a regression, > If so, maybe a bibisect could shed some light. Requesting. But it could also be that compilers' IEEE-754 handling or libraries changed somewhat. And that even both on Windows and Linux. i remember that @Mike Kaganski changed something recently, tdf#130725, acc. my limited understanding it shouldn't play into this issue, but maybe this or similar changes imported weaker behaviour for some cases ... in combination with former patches to circumvent former weaknesses? such is 'devs stage', one actual idea noted here for 'not to get lost': 0,5 ULP precision at granularity borders ... produces overlapping 'definition ranges'? as e.g. 0,5 ULP of 2^53 reaches 'down' to including '2^53 - 1' while 2^53-1 itself covers the range from 2^53-1-0,5 to 2^53-1+0,4999~, that leads to some ambiguity as e.g. 2^53-1+0,4 is falling into both 'ULP-precision-ranges', is that handeled in a meaningful way?
It would tremendously help if you refrained from writing prose and assumptions but instead restricted yourself to concise statements and examples not embedded in prose, and maybe even had a sense for syntax and grammar of language. It's tiring to read through your comments.
(In reply to b. from comment #2) > 0,5 ULP precision at > granularity borders ... produces overlapping 'definition ranges'? as e.g. > 0,5 ULP of 2^53 reaches 'down' to including '2^53 - 1' Say what? I don't understand what you are trying to tell. > while 2^53-1 itself > covers the range from 2^53-1-0,5 to 2^53-1+0,4999~, It does not cover a range. In an IEEE-754 binary64 magnitude with a distance precision of 1, i.e. values in the interval [2^52,2^53) are integers, any value or result that would lie in between integer values is tied to the nearest representable value. There is no range between two integer values in that interval. Btw, it would help people and specifically developers reading (and maybe trying) your examples if you used '.' decimal point instead of ',' decimal comma. > that leads to some > ambiguity as e.g. 2^53-1+0,4 is falling into both 'ULP-precision-ranges', is > that handeled in a meaningful way? What both "ULP-precision-ranges"? 2^53-1+0.4 results in 2^53-1 as that is the nearest representable value. There is no ambiguity.
ROUND(1/0.00001;-5) 0.00001 == 1e-5 => 1.0000000000000001e-05 1/0.00001 => 99999.999999999985 1/0.00001 * 1e-5 => 0.99999999999999989 + 0.5 + ... (correction value) => 1.5000000000000091 floor() => 1 / 1.0000000000000001e-05 => 99999.999999999985 In round(), using the inverse factor and swapping multiplication and division gives the proper result: 1/0.00001 / 1e5 => 0.99999999999999989 + 0.5 + 9e-15 (correction value) => 1.5000000000000091 floor() => 1 * 1e5 => 100000 For large values like ROUND(5e15+1;9) it could be determined if the value can be rounded at all.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/5abb1890ffafe5a2212076208a1c6e226f1ffa4e Resolves: tdf#138360 better accuracy in rtl_math_round() It will be available in 7.2.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-1": https://git.libreoffice.org/core/commit/0c1736f2dff63f2ac4a08c2b0e4c0d9c20d693cb Resolves: tdf#138360 better accuracy in rtl_math_round() It will be available in 7.1.0.0.beta2. 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.
hello @erAck, thank you for that improvement, i can confirm that '=ROUND(5e15+1;9)' yields better now, but i still see some 'air upwards', see below between '--------------- marks', (In reply to Eike Rathke from comment #3) > It's tiring to read through your comments. sorry, will try to improve my communication, for the moment i'd claim that this and other bugs would have stayed unnoticed without my way to look and think, and that the ways i look and think are somehow nested with my ways to communicate, (i already tried to improve by putting short statements before the 'prose') > You can't apply school math on IEEE-754 binary floating-point values over all ranges. Period. with all due respect: i can. IEEE and computers are not magic, but mathematics in formulas and silicon and electrons, just like programs that use them, if you use 'mathematics' with the same restrictions as used for real implementation in computers (64 bit, binary representation of values and so on), mathematics and computers should match, if they don't: bug, i'm aware (try to be) of the differences where 1.) - calc could benefit from e.g. better datatypes, to distinguish against 2.) - 'tricks' that could circumvent limitations, to distinguish against 3.) - the current program lagging behind the capabilities of IEEE 754, ------------------------------------------------------------------------ in this sense it would/should be possible to improve 'ROUND' further until e.g. '=ROUND(8796093022188;9)-8790000000000' results in '6093022188' instead of '6093022188,**00098**' as it doe's now, (it's only a sample, many other values affected as well) or ... did a little more 'poke around': '=round(2^52-1;-3)', '=round(2^52;-3)' and! '=ROUND(4503599627370491;-3)' should result in 4503599627370000, and not in: ^ 4503599627371000 (as they currently do, clearly a 'case 3.'), ^ insofar: partly fixed, shall i reopen or would you like a new bug? (stated with the assumption that the downloaded version from 2020-11-29 in the morning contains your patch, as the behaviour of '=ROUND(5e15+1;9)' changed it should, but pls. recheck) ------------------------------------------------------------------------ P.S. i have to correct a statement, not extensively tested but got the impression that 4.1.6.2 doe's not! do 'better calculations', but only 'different display' (other rounding or whatever), P.S. II > "ULP-precision-ranges"? - still thinking about it and just a little unsure if and what evolves from the difference that the '+/- 0,5 ULP ranges' for e.g. 2^53-2 and 2^53-1 are 'distinct', while [(2^53-1) - 0,5 .. (2^53-1) + 0,4999~] for 2^53-1 and [2^53 - 1,0 .. 2^53 + 0,9999~] for 2^53, are 'overlapping', do i remember right: "decimals are mapped to 'the nearest float'", and "floats are mapped to 'the shortest decimal string which maps back into their +/- 0,5 ULP representation range'"?
Verified in Version: 7.2.0.0.alpha0+ Build ID: 79ec66700266a22966d9e308a716be56c9c3a4a7 CPU threads: 4; OS: Linux 5.7; UI render: default; VCL: gtk3 Locale: en-US (en_US.UTF-8); UI: en-US Calc: threaded @Eike, thanks for fixing this issue. I've created a unittest in https://gerrit.libreoffice.org/c/core/+/106995
@Xisco: thanks, but as it is the implementation of rtl_math_round() in sal/rtl/math.cxx these should rather be tested in sal/qa/rtl/math/test-rtl-math.cxx
(In reply to Eike Rathke from comment #10) > @Xisco: thanks, but as it is the implementation of rtl_math_round() in > sal/rtl/math.cxx these should rather be tested in > sal/qa/rtl/math/test-rtl-math.cxx ok, thanks for the hint. Will try to do it that way then.
(In reply to b. from comment #8) > e.g. '=ROUND(8796093022188;9)-8790000000000' results in '6093022188' instead > of '6093022188,**00098**' as it doe's now Will be fixed with the next commit.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/49aff144c72a5258cf2ca392a0cfb7a31fb86819 Related: tdf#138360 Rounding integers to decimals is futile It will be available in 7.2.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-1": https://git.libreoffice.org/core/commit/1ad0ec4f10270eb44b558e6e8c6261d793fddb2f Related: tdf#138360 Rounding integers to decimals is futile It will be available in 7.1.0.0.beta2. 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/d8e0b1c81ffa16be8aae2231bcd3c02e8c01cf88 Typo in rounded digit string, tdf#138360 follow-up It will be available in 7.2.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-1": https://git.libreoffice.org/core/commit/db72eef56a99392af2579bb9a4026519e843c3bf Typo in rounded digit string, tdf#138360 follow-up It will be available in 7.1.0.0.beta2. 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.
(In reply to b. from comment #8) > '=ROUND(4503599627370491;-3)' should result in > 4503599627370000 Will be fixed with the next commit.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/deb119e415213716a76b9b489a700949c031c6fe Related: tdf#138360 Use approxFloor() in rtl_math_round() It will be available in 7.2.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-1": https://git.libreoffice.org/core/commit/84473267c5b77d12e3fa80a116995d645cc768c3 Related: tdf#138360 Use approxFloor() in rtl_math_round() It will be available in 7.1.0.0.beta2. 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.
Xisco Fauli committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/9467da7473b21006f748cea1f90e30e05ea32a1d tdf#138360: sal_rtl: Add unittest It will be available in 7.2.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/520949f17a91c531ea0c8b3856ffcf3c7ac8a3b2 Better accuracy in rtl_math_approxValue(), tdf#138360 related It will be available in 7.2.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-1": https://git.libreoffice.org/core/commit/73d98236ea83296018e6da30d0d7ec0219313776 Better accuracy in rtl_math_approxValue(), tdf#138360 related It will be available in 7.1.0.0.beta2. 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.
there are still a few small oddities remaining, e.g. '=RAWSUBTRACT(ROUND(9,00000000000001;15);9)' might result in '1,06581410364015E-14' as '=RAWSUBTRACT(ROUND(9,00000000000001;14);9)' and '=RAWSUBTRACT(ROUND(9,00000000000001;16);9)' do, instead of '1,24344978758018E-14' what calc actually calculates,
9.00000000000001 nearest representable binary is 9.000000000000010658141036401502788066864013671875 (1.001000000000000000000000000000000000000000000000011 * 2^3) In ROUND(9.00000000000001;15) multiplication with 1e15 yields 9000000000000011 on which adding 0.5 to round yields the nearest representation 9000000000000012, dividing back by 1e15 yields 9.0000000000000124 with nearest representation 9.0000000000000124344978758017532527446746826171875 (1.0010000000000000000000000000000000000000000000000111 * 2^3) note the one bit difference. We could additionally check if the intermediate to be rounded value is already an integer and skip +0.5 rounding to avoid jumping to the next representable value. The result of rounding after dividing back 1e15 would then be 9.0000000000000107 represented as 9.000000000000010658141036401502788066864013671875 (1.001000000000000000000000000000000000000000000000011 * 2^3) value identical to the input. The current result of RAWSUBTRACT(ROUND(9.00000000000001;15);9) is 1.2434497875801753e-14 displayed as 1.24344978758018E-14, with the modified rounding it would be 1.0658141036401503e-14 displayed as 1.06581410364015E-14
Eike Rathke committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/a10c33fdbe980effc3a14e773d1b94a14be7d428 Replace log2() call with parts.exponent-1023, tdf#138360 follow-up It will be available in 7.2.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/ecfcd99abd3f7dfe68a306dd8045d2da79e42d74 Check intermediate for not to be rounded value, tdf#138360 follow-up It will be available in 7.2.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-1": https://git.libreoffice.org/core/commit/ead1cdd23f5379f5cd8f69d5e73f410a67896db2 Check intermediate for not to be rounded value, tdf#138360 follow-up It will be available in 7.1.0.2. 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.
this: "9.00000000000001 nearest representable binary is 9.000000000000010658141036401502788066864013671875 (1.001000000000000000000000000000000000000000000000011 * 2^3) In ROUND(9.00000000000001;15) multiplication with 1e15 yields 9000000000000011" is a problem for itself, 'upgrading of deviations from necessary roundings (dec -> bin) to result errors in arithmetic operations - e.g. multiplications - of the rounded values ', the 'nearest representation' of 9.000000000001 with 128 bit 'quads' is 1.125000000000125 * 2^3, or binary: >1.00100000000000000000000000000000000001011010000100100110111000011010000100101011100110110000001000, >1.001000000000000000000000000000000000011 * 2^3 in the 64-bit version is rounded up to that extent, and the 'error' from that multiplies when multiplied, if we could get 1.1111111001011100111100101011111010000000001010 * 2^52 as result of multiplication by 1E15 ( 90000000000010.0) that would be much better and many problems much smaller ... ask me later if this might work ... but to check this and possibly show it, i need first a correctly calculating rounding, no matter with which crutches it is accomplished ... not adding 0,5 to 'already integers' (and then getting an unwanted 'stepup' to the next representable value) is a step in the correct direction, what about adding '0,5 minus one ULP', or '0,5 minus one bit'? (just thinking / guessing about it ... this fail doesn't harm bigger values (above 2^53 - 1)?? as the 0,5 'vanishes' in the granularity of '2'? or may 'work' there together with 'round to even'? but be off above 2^54?) other issues might evolve from different rounding strategies in different parts of the calculation? 'weitz' uses 'ties round to even? calc too? but usig the 'add 0,5 trick' to get the correct integer needs 'ties away from zero'? or 'ties towards positive infinity'?
it 'happened again', pasting of figures with some '0's failed, correction to previous comment: this: "9.00000000000001 nearest representable binary is 9.000000000000010658141036401502788066864013671875 (1.001000000000000000000000000000000000000000000000011 * 2^3) In ROUND(9.00000000000001;15) multiplication with 1e15 yields 9000000000000011" is a problem for itself, 'upgrading of deviations from necessary roundings (dec -> bin) to result errors in arithmetic operations - e.g. multiplications - of the rounded values ', the 'nearest representation' of 9.00000000000001 with 128 bit 'quads' is 1.12500000000000125 * 2^3, or binary: >1.0010000000000000000000000000000000000000000000000101101000010010011011100001101010000100101011100110110000001000 >1.0010000000000000000000000000000000000000000000000110 * 2^3 in the 64-bit version is rounded up to that extent, and the 'error' from that multiplies when multiplied, if we could get 1.1111111110010111001111001010111110101000000000001010 * 2^52 as result of multiplication by 1E15 ( 90000000000010.0) instead of 1.1111111110010111001111001010111110101000000000001011 * 2^52 that would be much better and many problems much smaller ... ask me later if this might work ... but to check this and possibly show it, i need first a correctly calculating rounding, no matter with which crutches it is accomplished ... not adding 0,5 to 'already integers' (and then getting an unwanted 'stepup' to the next representable value) is a step in the correct direction, what about adding '0,5 minus one ULP', or '0,5 minus one bit'? (just thinking / guessing about it ... this fail doesn't harm bigger values (above 2^53 - 1)?? as the 0,5 'vanishes' in the granularity of '2'? or may 'work' there together with 'round to even'? but be off above 2^54?) other issues might evolve from different rounding strategies in different parts of the calculation? 'weitz' uses 'round ties to even? calc too? but usig the 'add 0,5 trick' to get the correct integer needs 'ties away from zero'? or 'ties towards positive infinity'?
found a funny fail, can't explain yet where it evolves from, but tells me rounding isn't bullet-proof yet, '=ROUND(ROUND(99999999,7999999+0,12;C1)-99999999,7999995;C1)' and '=ROUND(99999999,7999999+ROUND(0,12-99999999,7999995;C1);C1)' produce mostly the same - correct? - results, while failing with 11, 12 and 17 as parameter in C1, you may even move the comma 8 digits to the left and see appr. every second value above 14 in C1 failing,
found a funny weakness again: i wanted to get 257,400000000000034... rounded, an 'a little odd' value resulting from '=256,1 + 1,3', wanted to get the fp-artifacts rounded away such that they don't sum up in further calculations ... rounding to 13 decimals looks meaningful (to save most as possible real value to get it working right also for other values), but: '=round(257.400000000000034;13)' results in 257.400000000000091, shown as 257.4000000000001, being rounded up! instead of down ... looks like - assumption - that for the decision 'round up / round down' it's not taken the 'real' deviation of 0,000000000000034 of the operand against a theoretical 'clean' 257,4000000000000000000... value, but that against the real world IEEE 754 fp-representation of 257,4 which is 257.399999999999977..., and that the sum of both deviations (0,000000000000034 and 0,000000000000023) being ~0,000000000000057 makes the decision and say's '>5' -> round up ... i don't know yet if there is an elegant way to get around such problems, 'litteral math with the strings'?, but got the idea it might be the source of plenty of similar problems, rounding being difficult at the value / artifacts borderline ... as it's really important to have a clean working round functionality for all these mod, '>', '=', '<' problems i'd like if plenty people think about it ... please! no comments like 'fp-math is imprecise, get used to it', it isn't, not and never to that degree as calcs results, and it makes sense to strive for improvements ... and please! no comments like 'round to smaller range', i know that would work, but it's just that what is needed ... get the knife between the meaningful value and preserve it accurately, and the fp-artifacts and get rid of them ... and that's at this point ... and ... it wouldn't help if the higher digits are other values than '0', think of x,999999999999934 ... besides: gnumeric (old windows version, portable 1.12.17 as well as fresh lin-version 1.12.48), same fail, excel (2010 64 bit) better result, ~00000000000000000, looks like already truncating the input,