Description: Tested on 7.5.1.2 (Win10) and on 6.4.7.2 (Linux) : The MROUND function applied on a number which ends by a 5 can produce different results dependings on the previous digit. Steps to Reproduce: 1. Type =MROUND(1.25,0.1) 2. Type =MROUND(1.45,0.1) 3. Actual Results: For =MROUND(1.25,0.1), result is 1.3 For =MROUND(1.45,0.1) result is 1.4 Expected Results: For =MROUND(1.25,0.1), expected result is 1.3 (as actual result) For =MROUND(1.45,0.1) expected result is 1.5 Reproducible: Always User Profile Reset: No Additional Info: Version: 7.5.1.2 (X86_64) / LibreOffice Community Build ID: fcbaee479e84c6cd81291587d2ee68cba099e129 CPU threads: 12; OS: Windows 10.0 Build 19045; UI render: Skia/Raster; VCL: win Locale: fr-FR (fr_FR); UI: fr-FR Calc: CL threaded
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.