sc/qa/unit/data/functions/financial/fods/irr.fods | 45 ++++++-------
sc/source/core/tool/interpr2.cxx | 73 +++++++++++++++++-----
2 files changed, 77 insertions(+), 41 deletions(-)
New commits:
commit a8216c210dbe83a7fe0c8b1a767c2ddf8b611e96
Author: Andreas Heinisch <andreas.heini...@yahoo.de>
AuthorDate: Mon Apr 12 10:51:57 2021 +0200
Commit: Eike Rathke <er...@redhat.com>
CommitDate: Wed Apr 21 16:59:13 2021 +0200
tdf#58585 - IRR function: support array argument for values
Text and empty cells in the value range are ignored.
Change-Id: I0790a83c79d3861afa855bf89a1c4eb39eb6d638
Reviewed-on: https://gerrit.libreoffice.org/c/core/+/113971
Tested-by: Jenkins
Reviewed-by: Eike Rathke <er...@redhat.com>
diff --git a/sc/qa/unit/data/functions/financial/fods/irr.fods
b/sc/qa/unit/data/functions/financial/fods/irr.fods
index ced44ffd0c49..4ecc9cb32581 100644
--- a/sc/qa/unit/data/functions/financial/fods/irr.fods
+++ b/sc/qa/unit/data/functions/financial/fods/irr.fods
@@ -1913,20 +1913,29 @@
<table:table-cell table:style-name="ce13"
table:number-columns-repeated="2"/>
<table:table-cell table:number-columns-repeated="9"/>
</table:table-row>
- <table:table-row table:style-name="ro7">
- <table:table-cell table:number-columns-repeated="2"/>
- <table:table-cell table:style-name="ce16"/>
- <table:table-cell/>
- <table:table-cell table:style-name="ce24"/>
- <table:table-cell/>
+ <table:table-row table:style-name="ro6">
+ <table:table-cell table:style-name="ce11"
table:formula="of:=IRR({-10000|5000|5000|5000})" office:value-type="percentage"
office:value="0.233751928528259" calcext:value-type="percentage">
+ <text:p>23,38%</text:p>
+ </table:table-cell>
+ <table:table-cell office:value-type="float"
office:value="0.233751928528259" calcext:value-type="float">
+ <text:p>0.233751928528259</text:p>
+ </table:table-cell>
+ <table:table-cell table:style-name="ce16"
table:formula="of:=[.A13]=[.B13]" 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="ce24"
table:formula="of:=FORMULA([.A13])" office:value-type="string"
office:string-value="=IRR({-10000|5000|5000|5000})" calcext:value-type="string">
+ <text:p>=IRR({-10000|5000|5000|5000})</text:p>
+ </table:table-cell>
+ <table:table-cell table:style-name="ce24" office:value-type="string"
calcext:value-type="string">
+ <text:p>Tdf#58585 enhancement</text:p>
+ </table:table-cell>
+ <table:table-cell table:number-columns-repeated="1"/>
<table:table-cell office:value-type="float" office:value="20"
calcext:value-type="float">
- <text:p>20</text:p>
+ <text:p>10</text:p>
</table:table-cell>
<table:table-cell table:number-columns-repeated="2"/>
- <table:table-cell table:style-name="ce29"/>
- <table:table-cell table:style-name="ce31"/>
- <table:table-cell table:style-name="ce29"
table:number-columns-repeated="3"/>
- <table:table-cell table:number-columns-repeated="6"/>
+ <table:table-cell table:style-name="ce13"
table:number-columns-repeated="2"/>
+ <table:table-cell table:number-columns-repeated="9"/>
</table:table-row>
<table:table-row table:style-name="ro7">
<table:table-cell table:style-name="ce12"/>
@@ -2014,20 +2023,6 @@
</table:table-cell>
<table:table-cell table:number-columns-repeated="15"/>
</table:table-row>
- <table:table-row table:style-name="ro7">
- <table:table-cell table:style-name="ce11" table:formula="of:=IRR(
{-10000|5000|5000|5000})" office:value-type="string" office:string-value=""
calcext:value-type="error">
- <text:p>Err:504</text:p>
- </table:table-cell>
- <table:table-cell/>
- <table:table-cell table:style-name="ce19"/>
- <table:table-cell table:style-name="ce24"
table:formula="of:=FORMULA([.A23])" office:value-type="string"
office:string-value="=IRR( {-10000;5000;5000;5000})"
calcext:value-type="string">
- <text:p>=IRR( {-10000;5000;5000;5000})</text:p>
- </table:table-cell>
- <table:table-cell table:style-name="ce24" office:value-type="string"
calcext:value-type="string">
- <text:p>Tdf#58585 enhancement</text:p>
- </table:table-cell>
- <table:table-cell table:number-columns-repeated="15"/>
- </table:table-row>
<table:table-row table:style-name="ro8">
<table:table-cell table:style-name="ce13"/>
<table:table-cell/>
diff --git a/sc/source/core/tool/interpr2.cxx b/sc/source/core/tool/interpr2.cxx
index a14c84b69f92..7e0d3733538d 100644
--- a/sc/source/core/tool/interpr2.cxx
+++ b/sc/source/core/tool/interpr2.cxx
@@ -1410,17 +1410,43 @@ void ScInterpreter::ScIRR()
fEstimated = GetDouble();
else
fEstimated = 0.1;
- sal_uInt16 sPos = sp; // memorize the position of the
stack
double fEps = 1.0;
double x, fValue;
if (fEstimated == -1.0)
x = 0.1; // default result for division by
zero
else
x = fEstimated; // startvalue
+
+ ScRange aRange;
+ ScMatrixRef pMat;
+ SCSIZE nC = 0;
+ SCSIZE nR = 0;
+ bool bIsMatrix = false;
switch (GetStackType())
{
- case svDoubleRef :
+ case svDoubleRef:
+ PopDoubleRef(aRange);
break;
+ case svMatrix:
+ case svExternalSingleRef:
+ case svExternalDoubleRef:
+ pMat = GetMatrix();
+ if (pMat)
+ {
+ pMat->GetDimensions(nC, nR);
+ if (nC == 0 || nR == 0)
+ {
+ PushIllegalParameter();
+ return;
+ }
+ bIsMatrix = true;
+ }
+ else
+ {
+ PushIllegalParameter();
+ return;
+ }
+ break;
default:
{
PushIllegalParameter();
@@ -1429,28 +1455,43 @@ void ScInterpreter::ScIRR()
}
const sal_uInt16 nIterationsMax = 20;
sal_uInt16 nItCount = 0;
- ScRange aRange;
- while (fEps > SCdEpsilon && nItCount < nIterationsMax)
+ FormulaError nIterError = FormulaError::NONE;
+ while (fEps > SCdEpsilon && nItCount < nIterationsMax && nGlobalError ==
FormulaError::NONE)
{ // Newtons method:
- sp = sPos; // reset stack
double fNom = 0.0;
double fDenom = 0.0;
- FormulaError nErr = FormulaError::NONE;
- PopDoubleRef( aRange );
- ScValueIterator aValIter(mrDoc, aRange, mnSubTotalFlags);
- if (aValIter.GetFirst(fValue, nErr))
- {
- double fCount = 0.0;
- fNom += fValue / pow(1.0+x,fCount);
- fDenom += -fCount * fValue / pow(1.0+x,fCount+1.0);
- fCount++;
- while ((nErr == FormulaError::NONE) && aValIter.GetNext(fValue,
nErr))
+ double fCount = 0.0;
+ if (bIsMatrix)
+ {
+ for (SCSIZE j = 0; j < nC && nGlobalError == FormulaError::NONE;
j++)
+ {
+ for (SCSIZE k = 0; k < nR; k++)
+ {
+ if (!pMat->IsValue(j, k))
+ continue;
+ fValue = pMat->GetDouble(j, k);
+ if (nGlobalError != FormulaError::NONE)
+ break;
+
+ fNom += fValue / pow(1.0+x,fCount);
+ fDenom += -fCount * fValue / pow(1.0+x,fCount+1.0);
+ fCount++;
+ }
+ }
+ }
+ else
+ {
+ ScValueIterator aValIter(mrDoc, aRange, mnSubTotalFlags);
+ bool bLoop = aValIter.GetFirst(fValue, nIterError);
+ while (bLoop && nIterError == FormulaError::NONE)
{
fNom += fValue / pow(1.0+x,fCount);
fDenom += -fCount * fValue / pow(1.0+x,fCount+1.0);
fCount++;
+
+ bLoop = aValIter.GetNext(fValue, nIterError);
}
- SetError(nErr);
+ SetError(nIterError);
}
double xNew = x - fNom / fDenom; // x(i+1) = x(i)-f(x(i))/f'(x(i))
nItCount++;
_______________________________________________
Libreoffice-commits mailing list
libreoffice-comm...@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-commits
