tdf#100767 make MIRR compliant with ODFF1.2

Support array argument for values.
At least one value must be positive and at least one value must be
negative.
Text and empty cells in the value range are ignored.

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

1 files changed, 95 insertions, 20 deletions

diff --git a/sc/source/core/tool/interpr2.cxx b/sc/source/core/tool/interpr2.cxx
index cc735270d4fd..8418062b8606 100644
--- a/sc/source/core/tool/interpr2.cxx
+++ b/sc/source/core/tool/interpr2.cxx
@@ -1393,46 +1393,121 @@ void ScInterpreter::ScIRR()
 void ScInterpreter::ScMIRR()
 {   // range_of_values ; rate_invest ; rate_reinvest
     nFuncFmtType = css::util::NumberFormat::PERCENT;
-    if( MustHaveParamCount( GetByte(), 3 ) )
+    if ( MustHaveParamCount( GetByte(), 3 ) )
     {
         double fRate1_reinvest = GetDouble() + 1;
         double fRate1_invest = GetDouble() + 1;
 
         ScRange aRange;
-        PopDoubleRef( aRange );
+        ScMatrixRef pMat;
+        SCSIZE nC = 0;
+        SCSIZE nR = 0;
+        bool bIsMatrix = false;
+        switch ( GetStackType() )
+        {
+            case svDoubleRef :
+                PopDoubleRef( aRange );
+                break;
+            case svMatrix :
+            case svExternalSingleRef:
+            case svExternalDoubleRef:
+                {
+                    pMat = GetMatrix();
+                    if ( pMat )
+                    {
+                        pMat->GetDimensions( nC, nR );
+                        if ( nC == 0 || nR == 0 )
+                            SetError( errIllegalArgument );
+                        bIsMatrix = true;
+                    }
+                    else
+                        SetError( errIllegalArgument );
+                }
+                break;
+            default :
+                SetError( errIllegalParameter );
+                break;
+        }
 
-        if( nGlobalError )
-            PushError( nGlobalError);
+        if ( nGlobalError )
+            PushError( nGlobalError );
         else
         {
             double fNPV_reinvest = 0.0;
             double fPow_reinvest = 1.0;
             double fNPV_invest = 0.0;
             double fPow_invest = 1.0;
-            ScValueIterator aValIter( pDok, aRange, mnSubTotalFlags );
-            double fCellValue;
             sal_uLong nCount = 0;
-            sal_uInt16 nIterError = 0;
+            bool bHasPosValue = false;
+            bool bHasNegValue = false;
 
-            bool bLoop = aValIter.GetFirst( fCellValue, nIterError );
-            while( bLoop )
+            if ( bIsMatrix )
             {
-                if( fCellValue > 0.0 )          // reinvestments
-                    fNPV_reinvest += fCellValue * fPow_reinvest;
-                else if( fCellValue < 0.0 )     // investments
-                    fNPV_invest += fCellValue * fPow_invest;
-                fPow_reinvest /= fRate1_reinvest;
-                fPow_invest /= fRate1_invest;
-                nCount++;
+                double fX;
+                for ( SCSIZE j = 0; j < nC; j++ )
+                {
+                    for ( SCSIZE k = 0; k < nR; ++k )
+                    {
+                        if ( !pMat->IsValue( j, k ) )
+                            continue;
+                        fX = pMat->GetDouble( j, k );
+                        if ( nGlobalError )
+                            break;
+
+                        if ( fX > 0.0 )
+                        {    // reinvestments
+                            bHasPosValue = true;
+                            fNPV_reinvest += fX * fPow_reinvest;
+                        }
+                        else if ( fX < 0.0 )
+                        {   // investments
+                            bHasNegValue = true;
+                            fNPV_invest += fX * fPow_invest;
+                        }
+                        fPow_reinvest /= fRate1_reinvest;
+                        fPow_invest /= fRate1_invest;
+                        nCount++;
+                    }
+                }
+            }
+            else
+            {
+                ScValueIterator aValIter( pDok, aRange, mnSubTotalFlags );
+                double fCellValue;
+                sal_uInt16 nIterError = 0;
+
+                bool bLoop = aValIter.GetFirst( fCellValue, nIterError );
+                while( bLoop )
+                {
+                    if( fCellValue > 0.0 )          // reinvestments
+                    {    // reinvestments
+                        bHasPosValue = true;
+                        fNPV_reinvest += fCellValue * fPow_reinvest;
+                    }
+                    else if( fCellValue < 0.0 )     // investments
+                    {   // investments
+                        bHasNegValue = true;
+                        fNPV_invest += fCellValue * fPow_invest;
+                    }
+                    fPow_reinvest /= fRate1_reinvest;
+                    fPow_invest /= fRate1_invest;
+                    nCount++;
+
+                    bLoop = aValIter.GetNext( fCellValue, nIterError );
+                }
 
-                bLoop = aValIter.GetNext( fCellValue, nIterError );
+                if ( nIterError )
+                    SetError( nIterError );
             }
-            if( nIterError )
-                PushError( nIterError );
+            if ( !( bHasPosValue && bHasNegValue ) )
+                SetError( errIllegalArgument );
+
+            if ( nGlobalError )
+                PushError( nGlobalError );
             else
             {
                 double fResult = -fNPV_reinvest / fNPV_invest;
-                fResult *= pow( fRate1_reinvest, (double) nCount - 1 );
+                fResult *= pow( fRate1_reinvest, (double)( nCount - 1 ) );
                 fResult = pow( fResult, div( 1.0, (nCount - 1)) );
                 PushDouble( fResult - 1.0 );
             }