tdf#124286 fix annoying rounding error.

In case of ROUNDDOWN and ROUNDUP, it is possible that seemingly clear decimal
values are rounded unexpectedly (from the user's POV). This is caused by the i
decimal to binary to decimal conversions.
By rounding to 12 significanr digits before calling the round-down of -up
function, most of these unexpected roundings are eliminated.

Change-Id: Ia19181383b77e1ff40a067c4a1cea1ece0955871
Reviewed-on: https://gerrit.libreoffice.org/69762
Tested-by: Jenkins
Reviewed-by: Eike Rathke <erack@redhat.com>

4 files changed, 66 insertions, 10 deletions

diff --git a/sc/qa/unit/data/functions/mathematical/fods/rounddown.fods b/sc/qa/unit/data/functions/mathematical/fods/rounddown.fods
index 5726eea4c9d5..0389f6348ef1 100644
--- a/sc/qa/unit/data/functions/mathematical/fods/rounddown.fods
+++ b/sc/qa/unit/data/functions/mathematical/fods/rounddown.fods
@@ -2123,6 +2123,23 @@
      <table:table-cell table:number-columns-repeated="5"/>
     </table:table-row>
     <table:table-row table:style-name="ro2">
+     <table:table-cell table:formula="of:=ROUNDDOWN(8.94-8;2)" office:value-type="float" office:value="0.94" calcext:value-type="float">
+      <text:p>0.9400000</text:p>
+     </table:table-cell>
+     <table:table-cell office:value-type="float" office:value="0.94" calcext:value-type="float">
+      <text:p>0.94</text:p>
+     </table:table-cell>
+     <table:table-cell table:style-name="ce30" table:formula="of:=[.A14]=[.B14]" office:value-type="boolean" office:boolean-value="true" calcext:value-type="boolean">
+      <text:p>TRUE</text:p>
+     </table:table-cell>
+     <table:table-cell table:style-name="ce38" table:formula="of:=FORMULA([.A14])" office:value-type="string" office:string-value="=ROUNDDOWN(8.94-8,2)" calcext:value-type="string">
+      <text:p>=ROUNDDOWN(31415.92654, 3)</text:p>
+     </table:table-cell>
+     <table:table-cell table:number-columns-repeated="5"/>
+     <table:table-cell table:style-name="ce47"/>
+     <table:table-cell table:number-columns-repeated="5"/>
+    </table:table-row>
+    <table:table-row table:style-name="ro2">
      <table:table-cell table:number-columns-repeated="2"/>
      <table:table-cell table:style-name="ce29"/>
      <table:table-cell table:style-name="ce38"/>
@@ -2408,4 +2425,4 @@
    </table:named-expressions>
   </office:spreadsheet>
  </office:body>
-</office:document>
\ No newline at end of file
+</office:document>
diff --git a/sc/qa/unit/data/functions/mathematical/fods/roundup.fods b/sc/qa/unit/data/functions/mathematical/fods/roundup.fods
index d1a56f7491cf..3fa289d4a2fc 100644
--- a/sc/qa/unit/data/functions/mathematical/fods/roundup.fods
+++ b/sc/qa/unit/data/functions/mathematical/fods/roundup.fods
@@ -2075,6 +2075,23 @@
      <table:table-cell table:number-columns-repeated="5"/>
     </table:table-row>
     <table:table-row table:style-name="ro2">
+     <table:table-cell table:formula="of:=ROUNDUP(8.06-8;2)" office:value-type="float" office:value="0.06" calcext:value-type="float">
+      <text:p>0.0600000</text:p>
+     </table:table-cell>
+     <table:table-cell office:value-type="float" office:value="0.06" calcext:value-type="float">
+      <text:p>0.06</text:p>
+     </table:table-cell>
+     <table:table-cell table:style-name="ce14" table:formula="of:=[.A14]=[.B14]" office:value-type="boolean" office:boolean-value="true" calcext:value-type="boolean">
+      <text:p>TRUE</text:p>
+     </table:table-cell>
+     <table:table-cell table:style-name="ce22" table:formula="of:=FORMULA([.A14])" office:value-type="string" office:string-value="=ROUNDUP(8.06-8,2)" calcext:value-type="string">
+      <text:p>=ROUNDUP(8.06-8,2)</text:p>
+     </table:table-cell>
+     <table:table-cell table:number-columns-repeated="5"/>
+     <table:table-cell table:style-name="ce31"/>
+     <table:table-cell table:number-columns-repeated="5"/>
+    </table:table-row>
+    <table:table-row table:style-name="ro2">
      <table:table-cell table:number-columns-repeated="2"/>
      <table:table-cell table:style-name="ce13"/>
      <table:table-cell table:style-name="ce22"/>
@@ -2360,4 +2377,4 @@
    </table:named-expressions>
   </office:spreadsheet>
  </office:body>
-</office:document>
\ No newline at end of file
+</office:document>
diff --git a/sc/source/core/inc/interpre.hxx b/sc/source/core/inc/interpre.hxx
index 3fd3dcf7668c..398fe707f86f 100644
--- a/sc/source/core/inc/interpre.hxx
+++ b/sc/source/core/inc/interpre.hxx
@@ -776,6 +776,7 @@ private:
     void ScConvertOOo();
     void ScEuroConvert();
     void ScRoundSignificant();
+    static void RoundSignificant( double fX, double fDigits, double &fRes );
 
     // financial functions
     void ScNPV();
diff --git a/sc/source/core/tool/interpr2.cxx b/sc/source/core/tool/interpr2.cxx
index b47acaddaf72..b8e4baef418f 100644
--- a/sc/source/core/tool/interpr2.cxx
+++ b/sc/source/core/tool/interpr2.cxx
@@ -983,10 +983,25 @@ void ScInterpreter::RoundNumber( rtl_math_RoundingMode eMode )
         else
         {
             sal_Int16 nDec = GetInt16();
+            double fX = GetDouble();
             if ( nGlobalError != FormulaError::NONE || nDec < -20 || nDec > 20 )
                 PushIllegalArgument();
             else
-                fVal = ::rtl::math::round( GetDouble(), nDec, eMode );
+            {
+                if ( ( eMode == rtl_math_RoundingMode_Down ||
+                       eMode == rtl_math_RoundingMode_Up ) &&
+                     nDec < 12 && fmod( fX, 1.0 ) != 0.0 )
+                {
+                    // tdf124286 : round to 12 significant digits before rounding
+                    //             down or up to avoid unexpected rounding errors
+                    //             caused by decimal -> binary -> decimal conversion
+                    double fRes;
+                    RoundSignificant( fX, 12, fRes );
+                    fVal = ::rtl::math::round( fRes, nDec, eMode );
+                }
+                else
+                    fVal = ::rtl::math::round( fX, nDec, eMode );
+            }
         }
         PushDouble(fVal);
     }
@@ -1007,6 +1022,17 @@ void ScInterpreter::ScRoundUp()
     RoundNumber( rtl_math_RoundingMode_Up );
 }
 
+void ScInterpreter::RoundSignificant( double fX, double fDigits, double &fRes )
+{
+    bool bNegVal = ( fX < 0 );
+    if ( bNegVal )
+        fX *= -1.0;
+    double fTemp = ::rtl::math::approxFloor( log10( fX ) ) + 1.0 - fDigits;
+    fRes = ::rtl::math::round( pow(10.0, -fTemp ) * fX ) * pow( 10.0, fTemp );
+    if ( bNegVal )
+        fRes *= -1.0;
+}
+
 // tdf#106931
 void ScInterpreter::ScRoundSignificant()
 {
@@ -1024,13 +1050,8 @@ void ScInterpreter::ScRoundSignificant()
             PushDouble( 0.0 );
         else
         {
-            bool bNegVal = ( fX < 0 );
-            if ( bNegVal )
-                fX *= -1.0;
-            double fTemp = ::rtl::math::approxFloor( log10( fX ) ) + 1.0 - fDigits;
-            double fRes = ::rtl::math::round( pow(10.0, -fTemp ) * fX ) * pow( 10.0, fTemp );
-            if ( bNegVal )
-                fRes *= -1.0;
+            double fRes;
+            RoundSignificant( fX, fDigits, fRes );
             PushDouble( fRes );
         }
     }