| Summary: | MROUND inconsistent behavior | ||
|---|---|---|---|
| Product: | LibreOffice | Reporter: | francksteel |
| Component: | Calc | Assignee: | Eike Rathke <erack> |
| Status: | RESOLVED FIXED | ||
| Severity: | normal | CC: | erack |
| Priority: | medium | ||
| Version: | Inherited From OOo | ||
| Hardware: | All | ||
| OS: | All | ||
| Whiteboard: | target:7.6.0 | ||
| Crash report or crash signature: | Regression By: | ||
| Attachments: | Spreadsheet testing various MROUND cases and showing results and expected results | ||
|
Description
francksteel
2023-04-20 16:01:45 UTC
Created attachment 186820 [details]
Spreadsheet testing various MROUND cases and showing results and expected results
This is not a bug. There is no "1.45" in Calc. When you enter this string into a cell, it gets converted to Calc's internal numeric representation, which is IEEE 754 double precision (64 bit) binary floating point. Its exact value is 1.4499999999999999555910790149937383830547332763671875 (you may see it at https://baseconvert.com/ieee-754-floating-point), and so the rounding is not from exactly 1.45, but from a slightly smaller value. Hebce the result. Please see also this FAQ: https://wiki.documentfoundation.org/Faq/Calc/Accuracy OTOH: Excel produces 1.5 in the latter case. Eike: is there anything we could improve? Thanks for your answers. I tested Python (3.11.3): Round(1.45,1) produces also 1.4 (same reason as in #2) Google Sheets MROUND gives the correct 1.5 LibreOffice Calc =ROUND(1.45,1) also produces the correct 1.5. (tested OK from 1.45 to 1.0000000045) So it is really the function MROUND that fails to produces the correct answer. Well, what does one expect, in MROUND(1.45;0.1) _both_ numbers have no exact representation 1.45 = 1.4499999999999999555910790149937383830547332763671875 0.1 = 0.1000000000000000055511151231257827021181583404541015625 and already 1.45/0.1 produces 14.499999999999998 I can take a look if we can introduce more scaling or approx there.. (In reply to Eike Rathke from comment #5) Indeed :) Possibly we could have some special case for a set of powers of 10 (i.e., their best approximations), so that when the second argument is exactly equal to double representation of 0.1, rtl::math::round(x, 1) is called? Nah, that's ugly ;) you'd probably run into similar things with some combinations with multiples of other not power of 2 values. Eike Rathke committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/90cbe03a0bd65151c197d3d4aeaf48a696f13fad Resolves: tdf#154941 MROUND() use approxValue of division to round 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. Xisco Fauli committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/8060f1349169772653c9be42cf64f559418fab73 tdf#154941: sc_mathematical_functions_test: Add unittest 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. |