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 <erack@redhat.com>

2 files changed, 77 insertions, 41 deletions

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++;