As explained in VBA documentation for Round [1]: > This VBA function returns something commonly referred to as bankers rounding. [MS-VBAL] [2] defined Banker's rounding as alternative name for "round-to-even"; it is one of rounding modes defined in IEEE 754, its recommended rounding mode for decimal [3]. [1] also provides some examples demonstrating the values returned bu VBA's Round: > ?Round(0.12335,4) > 0,1234 > ?Round(0.12345,4) > 0,1234 > ?Round(0.12355,4) > 0,1236 > ?Round(0.12365,4) > 0,1236 However, LibreOffice's Round function [4] implementation [5] does not implement the required rounding mode. Executing the example calculations mentioned above gives 0.1234, 0.1235, 0.1236, 0.1237. FTR: we have a rtl_math_round function, that accepts a flag defining rounding mode, rtl_math_RoundingMode_HalfEven among the possible values, which may be used in the VBA function implementation. [1] https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/round-function [2] https://docs.microsoft.com/en-us/openspecs/microsoft_general_purpose_programming_languages/ms-vbal/98152b5a-4d86-4acb-b875-66cb1f49433e [3] https://en.wikipedia.org/wiki/IEEE_754#Rounding_rules [4] https://help.libreoffice.org/7.2/en-US/text/sbasic/shared/03170000.html?DbPAR=BASIC [5] https://opengrok.libreoffice.org/xref/core/basic/source/runtime/methods1.cxx?r=86d298f8&mo=63707&fi=2377#2377 [6] https://opengrok.libreoffice.org/xref/core/sal/rtl/math.cxx?r=49af7e22&mo=38462&fi=1130#1130
The fix to this easyhack must include a unit test. Please CC me to the respective gerrit change, so that I could review and assist.
mmmmhhhh ... Σκύλλα και Χάρυβδις (Skylla and Charybdis) ... Excel round(x,n) in a sheet: '=round(0.12335,4)' -> 0.1234 '=round(0.12345,4)' -> 0.123*5* '=round(0.12355,4)' -> 0.1236 '=round(0.12365,4)' -> 0.123*7* Excel round(x,n) in VBA macros: '=round(0.12335,4)' -> 0.1234 '=round(0.12345,4)' -> 0.123*4* '=round(0.12355,4)' -> 0.1236 '=round(0.12365,4)' -> 0.123*6* in one! program and its associated macro language ... mentally healthy people might call things like this unhealthy, either we copy this ambiguity -> discrepancies between sheet and macro -> irritated users, or we do something with less internal contradictions -> compromises Excel compatibility -> irritated users, any idea for a third way?
Please ignore the above spam comment by a person who doesn't understand that VBA compatibility functions are created for compatibility with, and must follow the specifications of, VBA.
I'll start working on this.
baltasarq committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/0eacbfa75132724faaeeb9ea22a1c3e44eefb515 tdf#142922 fixes Round to follow half-to-even rounding It will be available in 7.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.
baltasarq committed a patch related to this issue. It has been pushed to "libreoffice-7-2": https://git.libreoffice.org/core/commit/70e86db58bd04d919b3f67efad6c94dc967fd900 tdf#142922 fixes Round to follow half-to-even rounding It will be available in 7.2.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.