tdf#97831 [part] Add Excel 2016 functions to Calc

Functions MINIFS and MAXIFS

Change-Id: I4bd2e8b82f8377af81f4373d0c33ac286588b8df
Reviewed-on: https://gerrit.libreoffice.org/24619
Tested-by: Jenkins <ci@libreoffice.org>
Reviewed-by: Eike Rathke <erack@redhat.com>
Tested-by: Eike Rathke <erack@redhat.com>

9 files changed, 120 insertions, 4 deletions

diff --git a/sc/inc/helpids.h b/sc/inc/helpids.h
index db24bed2e6d6..9c15904016be 100644
--- a/sc/inc/helpids.h
+++ b/sc/inc/helpids.h
@@ -640,5 +640,7 @@
 #define HID_FUNC_TEXTJOIN_MS                                    "SC_HID_FUNC_TEXTJOIN_MS"
 #define HID_FUNC_IFS_MS                                         "SC_HID_FUNC_IFS_MS"
 #define HID_FUNC_SWITCH_MS                                      "SC_HID_FUNC_SWITCH_MS"
+#define HID_FUNC_MINIFS_MS                                      "SC_HID_FUNC_MINIFS_MS"
+#define HID_FUNC_MAXIFS_MS                                      "SC_HID_FUNC_MAXIFS_MS"
 
 /* vim:set shiftwidth=4 softtabstop=4 expandtab: */
diff --git a/sc/qa/unit/ucalc.cxx b/sc/qa/unit/ucalc.cxx
index d3e7567b458c..228fafa23ef4 100644
--- a/sc/qa/unit/ucalc.cxx
+++ b/sc/qa/unit/ucalc.cxx
@@ -2688,9 +2688,11 @@ void Test::testFunctionLists()
         "LOGNORMDIST",
         "MAX",
         "MAXA",
+        "MAXIFS",
         "MEDIAN",
         "MIN",
         "MINA",
+        "MINIFS",
         "MODE",
         "MODE.MULT",
         "MODE.SNGL",
diff --git a/sc/source/core/inc/interpre.hxx b/sc/source/core/inc/interpre.hxx
index 8321e3b18748..a01b60bcb937 100644
--- a/sc/source/core/inc/interpre.hxx
+++ b/sc/source/core/inc/interpre.hxx
@@ -100,7 +100,9 @@ enum ScIterFuncIfs
 {
     ifSUMIFS,     // Multi-Conditional sum
     ifAVERAGEIFS, // Multi-Conditional average
-    ifCOUNTIFS    // Multi-Conditional count
+    ifCOUNTIFS,   // Multi-Conditional count
+    ifMINIFS,     // Multi-Conditional minimum
+    ifMAXIFS      // Multi-Conditional maximum
 };
 
 enum ScETSType
@@ -608,6 +610,8 @@ void ScConcat_MS();
 void ScTextJoin_MS();
 void ScIfs_MS();
 void ScSwitch_MS();
+void ScMinIfs_MS();
+void ScMaxIfs_MS();
 void ScExternal();
 void ScMissing();
 void ScMacro();
diff --git a/sc/source/core/tool/interpr1.cxx b/sc/source/core/tool/interpr1.cxx
index 0a5ed78dfc17..dcaf3746e18b 100644
--- a/sc/source/core/tool/interpr1.cxx
+++ b/sc/source/core/tool/interpr1.cxx
@@ -5249,6 +5249,8 @@ double ScInterpreter::IterateParametersIfs( ScIterFuncIfs eFunc )
         double fMem = 0.0;
         double fRes = 0.0;
         double fCount = 0.0;
+        double fMin = std::numeric_limits<double>::max();
+        double fMax = std::numeric_limits<double>::min();
         short nParam = 1;
         size_t nRefInList = 0;
         SCCOL nDimensionCols = 0;
@@ -5484,7 +5486,7 @@ double ScInterpreter::IterateParametersIfs( ScIterFuncIfs eFunc )
         if (nGlobalError)
             return 0;   // bail out
 
-        // main range - only for AVERAGEIFS and SUMIFS
+        // main range - only for AVERAGEIFS, SUMIFS, MINIFS and MAXIFS
         if (nParamCount == 1)
         {
             nParam = 1;
@@ -5587,6 +5589,10 @@ double ScInterpreter::IterateParametersIfs( ScIterFuncIfs eFunc )
                     }
                     else
                         fSum += fVal;
+                    if ( fMin > fVal )
+                        fMin = fVal;
+                    if ( fMax < fVal )
+                        fMax = fVal;
                 }
             }
             else
@@ -5612,6 +5618,10 @@ double ScInterpreter::IterateParametersIfs( ScIterFuncIfs eFunc )
                                 }
                                 else
                                     fSum += fVal;
+                                if ( fMin > fVal )
+                                    fMin = fVal;
+                                if ( fMax < fVal )
+                                    fMax = fVal;
                             }
                         }
                     }
@@ -5631,6 +5641,8 @@ double ScInterpreter::IterateParametersIfs( ScIterFuncIfs eFunc )
             case ifSUMIFS:     fRes = ::rtl::math::approxAdd( fSum, fMem ); break;
             case ifAVERAGEIFS: fRes = div( ::rtl::math::approxAdd( fSum, fMem ), fCount); break;
             case ifCOUNTIFS:   fRes = fCount; break;
+            case ifMINIFS:     fRes = ( fMin < std::numeric_limits<double>::max() ? fMin : 0 ); break;
+            case ifMAXIFS:     fRes = ( fMax > std::numeric_limits<double>::min() ? fMax : 0 ); break;
             default: ; // nothing
         }
         return fRes;
diff --git a/sc/source/core/tool/interpr4.cxx b/sc/source/core/tool/interpr4.cxx
index 684913b3601a..e6985dc5447e 100644
--- a/sc/source/core/tool/interpr4.cxx
+++ b/sc/source/core/tool/interpr4.cxx
@@ -3944,6 +3944,8 @@ StackVar ScInterpreter::Interpret()
                 case ocTextJoin_MS      : ScTextJoin_MS();              break;
                 case ocIfs_MS           : ScIfs_MS();                   break;
                 case ocSwitch_MS        : ScSwitch_MS();                break;
+                case ocMinIfs_MS        : ScMinIfs_MS();                break;
+                case ocMaxIfs_MS        : ScMaxIfs_MS();                break;
                 case ocMatValue         : ScMatValue();                 break;
                 case ocMatrixUnit       : ScEMat();                     break;
                 case ocMatDet           : ScMatDet();                   break;
diff --git a/sc/source/core/tool/interpr8.cxx b/sc/source/core/tool/interpr8.cxx
index 5cb33f06e6db..aa7088624944 100644
--- a/sc/source/core/tool/interpr8.cxx
+++ b/sc/source/core/tool/interpr8.cxx
@@ -2019,4 +2019,16 @@ void ScInterpreter::ScSwitch_MS()
         PushError( errUnknownStackVariable );
 }
 
+
+void ScInterpreter::ScMinIfs_MS()
+{
+    PushDouble( IterateParametersIfs( ifMINIFS ) );
+}
+
+
+void ScInterpreter::ScMaxIfs_MS()
+{
+    PushDouble( IterateParametersIfs( ifMAXIFS ) );
+}
+
 /* vim:set shiftwidth=4 softtabstop=4 expandtab: */
diff --git a/sc/source/filter/excel/xlformula.cxx b/sc/source/filter/excel/xlformula.cxx
index dadb2e7d8bad..c79a432c0601 100644
--- a/sc/source/filter/excel/xlformula.cxx
+++ b/sc/source/filter/excel/xlformula.cxx
@@ -583,7 +583,9 @@ static const XclFunctionInfo saFuncTable_2016[] =
     EXC_FUNCENTRY_V_VR(  ocConcat_MS,           1,  MX, 0,  "CONCAT" ),
     EXC_FUNCENTRY_V_VR(  ocTextJoin_MS,         3,  MX, 0,  "TEXTJOIN" ),
     EXC_FUNCENTRY_V_VR(  ocIfs_MS,              2,  MX, 0,  "IFS" ),
-    EXC_FUNCENTRY_V_VR(  ocSwitch_MS,           3,  MX, 0,  "SWITCH" )
+    EXC_FUNCENTRY_V_VR(  ocSwitch_MS,           3,  MX, 0,  "SWITCH" ),
+    EXC_FUNCENTRY_V_VR(  ocMinIfs_MS,           3,  MX, 0,  "MINIFS" ),
+    EXC_FUNCENTRY_V_VR(  ocMaxIfs_MS,           3,  MX, 0,  "MAXIFS" )
 };
 
 #define EXC_FUNCENTRY_ODF( opcode, minparam, maxparam, flags, asciiname ) \
diff --git a/sc/source/filter/oox/formulabase.cxx b/sc/source/filter/oox/formulabase.cxx
index c4636305bcaf..3778f2c1a582 100644
--- a/sc/source/filter/oox/formulabase.cxx
+++ b/sc/source/filter/oox/formulabase.cxx
@@ -913,7 +913,9 @@ static const FunctionData saFuncTable2016[] =
     { "COM.MICROSOFT.CONCAT",                   "CONCAT",                   NOID,   NOID,   1,  MX, V, { VR }, FUNCFLAG_MACROCALL_NEW },
     { "COM.MICROSOFT.TEXTJOIN",                 "TEXTJOIN",                 NOID,   NOID,   3,  MX, V, { VR }, FUNCFLAG_MACROCALL_NEW },
     { "COM.MICROSOFT.IFS",                      "IFS",                      NOID,   NOID,   2,  MX, R, { VO, RO }, FUNCFLAG_MACROCALL_NEW },
-    { "COM.MICROSOFT.SWITCH",                   "SWITCH",                   NOID,   NOID,   3,  MX, R, { VO, RO }, FUNCFLAG_MACROCALL_NEW }
+    { "COM.MICROSOFT.SWITCH",                   "SWITCH",                   NOID,   NOID,   3,  MX, R, { VO, RO }, FUNCFLAG_MACROCALL_NEW },
+    { "COM.MICROSOFT.MINIFS",                   "MINIFS",                   NOID,   NOID,   3,  MX, R, { VO, RO }, FUNCFLAG_MACROCALL_NEW },
+    { "COM.MICROSOFT.MAXIFS",                   "MAXIFS",                   NOID,   NOID,   3,  MX, R, { VO, RO }, FUNCFLAG_MACROCALL_NEW }
 };
 
 
diff --git a/sc/source/ui/src/scfuncs.src b/sc/source/ui/src/scfuncs.src
index 04c9388985c2..4ce4a7590502 100644
--- a/sc/source/ui/src/scfuncs.src
+++ b/sc/source/ui/src/scfuncs.src
@@ -11663,6 +11663,84 @@ Resource RID_SC_FUNCTION_DESCRIPTIONS2
             Text [ en-US ] = "Value to return when corresponding value argument matches expression." ;
         };
     };
+    Resource SC_OPCODE_MINIFS_MS
+    {
+        String 1 // Description
+        {
+            Text [ en-US ] = "Returns the minimum value in a range that meet multiple criteria in multiple ranges." ;
+        };
+        ExtraData =
+        {
+            0;
+            ID_FUNCTION_GRP_STATISTIC;
+            HID_FUNC_MINIFS_MS;
+            PAIRED_VAR_ARGS + 1;  0; 0; 0;
+            0;
+        };
+        String 2 // Name of Parameter 1
+        {
+            Text [ en-US ] = "min_range" ;
+        };
+        String 3 // Description of Parameter 1
+        {
+            Text [ en-US ] = "The range from which the minimum will be determined." ;
+        };
+        String 4 // Name of Parameter 2
+        {
+            Text [ en-US ] = "range" ;
+        };
+        String 5 // Description of Parameter 2
+        {
+            Text [ en-US ] = "The range from which the minimum value is to be taken." ;
+        };
+        String 6 // Name of Parameter 3
+        {
+            Text [ en-US ] = "criteria" ;
+        };
+        String 7 // Description of Parameter 3
+        {
+            Text [ en-US ] = "Criteria 1, criteria 2,... are the criteria to be applied to the ranges given." ;
+        };
+    };
+    Resource SC_OPCODE_MAXIFS_MS
+    {
+        String 1 // Description
+        {
+            Text [ en-US ] = "Returns the maximum value in a range that meet multiple criteria in multiple ranges." ;
+        };
+        ExtraData =
+        {
+            0;
+            ID_FUNCTION_GRP_STATISTIC;
+            HID_FUNC_MAXIFS_MS;
+            PAIRED_VAR_ARGS + 1;  0; 0; 0;
+            0;
+        };
+        String 2 // Name of Parameter 1
+        {
+            Text [ en-US ] = "max_range" ;
+        };
+        String 3 // Description of Parameter 1
+        {
+            Text [ en-US ] = "The range from which the maximum will be determined." ;
+        };
+        String 4 // Name of Parameter 2
+        {
+            Text [ en-US ] = "range" ;
+        };
+        String 5 // Description of Parameter 2
+        {
+            Text [ en-US ] = "The range from which the minimum value is to be taken." ;
+        };
+        String 6 // Name of Parameter 3
+        {
+            Text [ en-US ] = "criteria" ;
+        };
+        String 7 // Description of Parameter 3
+        {
+            Text [ en-US ] = "Criteria 1, criteria 2,... are the criteria to be applied to the ranges given." ;
+        };
+    };
      // -=*# Resource for function EXACT #*=-
     Resource SC_OPCODE_EXACT
     {