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>
diff --git a/sc/qa/unit/data/functions/mathematical/fods/rounddown.fods b/sc/qa/unit/data/functions/mathematical/fods/rounddown.fods
index 5726eea..0389f63 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 d1a56f7..3fa289d 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 3fd3dcf..398fe70 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 b47acad..b8e4bae 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 );
}
}
