summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorWinfried Donkers <winfrieddonkers@libreoffice.org>2017-03-05 13:13:36 +0100
committerEike Rathke <erack@redhat.com>2017-03-06 16:45:25 +0000
commit190eaa760336ec7c8f2d8d89785b22e770b3e8d6 (patch)
tree3ee7d9c2a43d9e0e4cef4f6cc90a6b87c3b3fe1e
parent67bff281f9befbea8b4ff028fabe056b8c4e184b (diff)
Check for divide by 0 in Calc function NPER.
Plus improve efficiency and use correct prefixes for variable names. Change-Id: I2ba0863a57aad4c89bc930fa69b08ed90b070002 Reviewed-on: https://gerrit.libreoffice.org/34902 Tested-by: Jenkins <ci@libreoffice.org> Reviewed-by: Eike Rathke <erack@redhat.com>
-rw-r--r--sc/qa/unit/data/functions/financial/fods/nper.fods40
-rw-r--r--sc/source/core/tool/interpr2.cxx26
2 files changed, 49 insertions, 17 deletions
diff --git a/sc/qa/unit/data/functions/financial/fods/nper.fods b/sc/qa/unit/data/functions/financial/fods/nper.fods
index 3911ba9292e7..22c06f914a25 100644
--- a/sc/qa/unit/data/functions/financial/fods/nper.fods
+++ b/sc/qa/unit/data/functions/financial/fods/nper.fods
@@ -1528,16 +1528,42 @@
<table:table-cell table:number-columns-repeated="9"/>
</table:table-row>
<table:table-row table:style-name="ro6">
- <table:table-cell table:number-columns-repeated="2"/>
- <table:table-cell table:style-name="ce25"/>
- <table:table-cell table:number-columns-repeated="6"/>
+ <table:table-cell table:style-name="ce48" table:formula="of:=NPER(0.06/52; 0; 8000; 0 ;2)" office:value-type="string" office:string-value="" calcext:value-type="error">
+ <text:p>Err:502</text:p>
+ </table:table-cell>
+ <table:table-cell table:formula="of:#ERR502!" office:value-type="string" office:string-value="" calcext:value-type="error">
+ <text:p>Err:502</text:p>
+ </table:table-cell>
+ <table:table-cell table:style-name="ce53" table:formula="of:=ORG.OPENOFFICE.ERRORTYPE([.A12])=502" office:value-type="boolean" office:boolean-value="true" calcext:value-type="boolean">
+ <text:p>TRUE</text:p>
+ </table:table-cell>
+ <table:table-cell table:formula="of:=FORMULA([.A12])" office:value-type="string" office:string-value="=NPER(0.06/52, 0, 8000, 0 ,2)" calcext:value-type="string">
+ <text:p>=NPER(0.06/52, 0, 8000, 0 ,2)</text:p>
+ </table:table-cell>
+ <table:table-cell office:value-type="string" calcext:value-type="string">
+ <text:p>No payment, no result</text:p>
+ </table:table-cell>
+ <table:table-cell table:number-columns-repeated="4"/>
<table:table-cell table:style-name="ce20" table:number-columns-repeated="2"/>
<table:table-cell table:number-columns-repeated="9"/>
</table:table-row>
<table:table-row table:style-name="ro6">
- <table:table-cell table:number-columns-repeated="2"/>
- <table:table-cell table:style-name="ce26"/>
- <table:table-cell table:number-columns-repeated="6"/>
+ <table:table-cell table:formula="of:=NPER(0.06/52; -175;8000;-8000;1)" office:value-type="float" office:value="0" calcext:value-type="float">
+ <text:p>0</text:p>
+ </table:table-cell>
+ <table:table-cell office:value-type="float" office:value="0" calcext:value-type="float">
+ <text:p>0</text:p>
+ </table:table-cell>
+ <table:table-cell table:style-name="ce56" table:formula="of:=ROUND([.A13];12)=ROUND([.B13];12)" office:value-type="boolean" office:boolean-value="true" calcext:value-type="boolean">
+ <text:p>TRUE</text:p>
+ </table:table-cell>
+ <table:table-cell table:formula="of:=FORMULA([.A13])" office:value-type="string" office:string-value="=NPER(0.06/52, -175,8000,-8000,1)" calcext:value-type="string">
+ <text:p>=NPER(0.06/52, -175,8000,-8000,1)</text:p>
+ </table:table-cell>
+ <table:table-cell office:value-type="string" calcext:value-type="string">
+ <text:p>&apos;present value = future value’</text:p>
+ </table:table-cell>
+ <table:table-cell table:number-columns-repeated="4"/>
<table:table-cell table:style-name="ce41"/>
<table:table-cell table:style-name="ce43"/>
<table:table-cell table:style-name="ce41" table:number-columns-repeated="3"/>
@@ -1760,4 +1786,4 @@
</table:named-expressions>
</office:spreadsheet>
</office:body>
-</office:document> \ No newline at end of file
+</office:document>
diff --git a/sc/source/core/tool/interpr2.cxx b/sc/source/core/tool/interpr2.cxx
index cf305836b3a2..a25da2993368 100644
--- a/sc/source/core/tool/interpr2.cxx
+++ b/sc/source/core/tool/interpr2.cxx
@@ -1995,7 +1995,7 @@ void ScInterpreter::ScFV()
void ScInterpreter::ScNper()
{
- double nInterest, nRmz, nBw, nZw = 0;
+ double fInterest, fPmt, fPV, fFV = 0;
bool bPayInAdvance = false;
sal_uInt8 nParamCount = GetByte();
if ( !MustHaveParamCount( nParamCount, 3, 5 ) )
@@ -2003,17 +2003,23 @@ void ScInterpreter::ScNper()
if (nParamCount == 5)
bPayInAdvance = GetBool();
if (nParamCount >= 4)
- nZw = GetDouble();
- nBw = GetDouble();
- nRmz = GetDouble();
- nInterest = GetDouble();
- if (nInterest == 0.0)
- PushDouble(-(nBw + nZw)/nRmz);
+ fFV = GetDouble(); // Future Value
+ fPV = GetDouble(); // Present Value
+ fPmt = GetDouble(); // Payment
+ fInterest = GetDouble();
+ // Note that due to the function specification in ODFF1.2 (and Excel) the
+ // amount to be paid to get from fPV to fFV is fFV_+_fPV.
+ if ( fPV + fFV == 0.0 )
+ PushDouble( 0.0 );
+ else if ( fPmt == 0.0 )
+ PushIllegalArgument(); // No payment, future value can never be reached
+ else if (fInterest == 0.0)
+ PushDouble(-(fPV + fFV)/fPmt);
else if (bPayInAdvance)
- PushDouble(log(-(nInterest*nZw-nRmz*(1.0+nInterest))/(nInterest*nBw+nRmz*(1.0+nInterest)))
- / rtl::math::log1p(nInterest));
+ PushDouble(log(-(fInterest*fFV-fPmt*(1.0+fInterest))/(fInterest*fPV+fPmt*(1.0+fInterest)))
+ / rtl::math::log1p(fInterest));
else
- PushDouble(log(-(nInterest*nZw-nRmz)/(nInterest*nBw+nRmz)) / rtl::math::log1p(nInterest));
+ PushDouble(log(-(fInterest*fFV-fPmt)/(fInterest*fPV+fPmt)) / rtl::math::log1p(fInterest));
}
bool ScInterpreter::RateIteration( double fNper, double fPayment, double fPv,