summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorWinfried Donkers <winfrieddonkers@libreoffice.org>2016-05-03 21:19:19 +0200
committerEike Rathke <erack@redhat.com>2016-05-04 21:54:01 +0000
commitc57dc741a6f221f53f3c8da2f521c6ece63246c2 (patch)
treeded3a12b1dbb08fe920045fadf09a2e877e6145d
parent75ac19149d21f5164e7bb3ea1b114670e1d7859e (diff)
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>
-rw-r--r--formula/source/core/resource/core_resource.src14
-rw-r--r--include/formula/compiler.hrc4
-rw-r--r--include/formula/opcode.hxx2
-rw-r--r--sc/inc/helpids.h2
-rw-r--r--sc/qa/unit/ucalc.cxx2
-rw-r--r--sc/source/core/inc/interpre.hxx6
-rw-r--r--sc/source/core/tool/interpr1.cxx14
-rw-r--r--sc/source/core/tool/interpr4.cxx2
-rw-r--r--sc/source/core/tool/interpr8.cxx12
-rw-r--r--sc/source/filter/excel/xlformula.cxx4
-rw-r--r--sc/source/filter/oox/formulabase.cxx4
-rw-r--r--sc/source/ui/src/scfuncs.src78
12 files changed, 139 insertions, 5 deletions
diff --git a/formula/source/core/resource/core_resource.src b/formula/source/core/resource/core_resource.src
index acc6ba899dab..551de2fa1c74 100644
--- a/formula/source/core/resource/core_resource.src
+++ b/formula/source/core/resource/core_resource.src
@@ -291,6 +291,8 @@ Resource RID_STRLIST_FUNCTION_NAMES_ENGLISH_ODFF
String SC_OPCODE_TEXTJOIN_MS { Text = "COM.MICROSOFT.TEXTJOIN" ; };
String SC_OPCODE_IFS_MS { Text = "COM.MICROSOFT.IFS" ; };
String SC_OPCODE_SWITCH_MS { Text = "COM.MICROSOFT.SWITCH" ; };
+ String SC_OPCODE_MINIFS_MS { Text = "COM.MICROSOFT.MINIFS" ; };
+ String SC_OPCODE_MAXIFS_MS { Text = "COM.MICROSOFT.MAXIFS" ; };
String SC_OPCODE_MAT_VALUE { Text = "MVALUE" ; };
String SC_OPCODE_MAT_DET { Text = "MDETERM" ; };
String SC_OPCODE_MAT_INV { Text = "MINVERSE" ; };
@@ -729,6 +731,8 @@ Resource RID_STRLIST_FUNCTION_NAMES_ENGLISH_OOXML
String SC_OPCODE_TEXTJOIN_MS { Text = "_xlfn.TEXTJOIN" ; };
String SC_OPCODE_IFS_MS { Text = "_xlfn.IFS" ; };
String SC_OPCODE_SWITCH_MS { Text = "_xlfn.SWITCH" ; };
+ String SC_OPCODE_MINIFS_MS { Text = "_xlfn.MINIFS" ; };
+ String SC_OPCODE_MAXIFS_MS { Text = "_xlfn.MAXIFS" ; };
String SC_OPCODE_MAT_VALUE { Text = "MVALUE" ; };
String SC_OPCODE_MAT_DET { Text = "MDETERM" ; };
String SC_OPCODE_MAT_INV { Text = "MINVERSE" ; };
@@ -1167,6 +1171,8 @@ Resource RID_STRLIST_FUNCTION_NAMES_ENGLISH
String SC_OPCODE_TEXTJOIN_MS { Text = "TEXTJOIN" ; };
String SC_OPCODE_IFS_MS { Text = "IFS" ; };
String SC_OPCODE_SWITCH_MS { Text = "SWITCH" ; };
+ String SC_OPCODE_MINIFS_MS { Text = "MINIFS" ; };
+ String SC_OPCODE_MAXIFS_MS { Text = "MAXIFS" ; };
String SC_OPCODE_MAT_VALUE { Text = "MVALUE" ; };
String SC_OPCODE_MAT_DET { Text = "MDETERM" ; };
String SC_OPCODE_MAT_INV { Text = "MINVERSE" ; };
@@ -2349,6 +2355,14 @@ Resource RID_STRLIST_FUNCTION_NAMES
{
Text [ en-US ] = "SWITCH" ;
};
+ String SC_OPCODE_MINIFS_MS
+ {
+ Text [ en-US ] = "MINIFS" ;
+ };
+ String SC_OPCODE_MAXIFS_MS
+ {
+ Text [ en-US ] = "MAXIFS" ;
+ };
String SC_OPCODE_MAT_VALUE
{
Text [ en-US ] = "MVALUE" ;
diff --git a/include/formula/compiler.hrc b/include/formula/compiler.hrc
index 0bb83608e28b..7a7bfe74ab94 100644
--- a/include/formula/compiler.hrc
+++ b/include/formula/compiler.hrc
@@ -499,7 +499,9 @@
#define SC_OPCODE_TEXTJOIN_MS 488
#define SC_OPCODE_IFS_MS 489
#define SC_OPCODE_SWITCH_MS 490
-#define SC_OPCODE_STOP_2_PAR 491 /* last function with two or more parameters' OpCode + 1 */
+#define SC_OPCODE_MINIFS_MS 491
+#define SC_OPCODE_MAXIFS_MS 492
+#define SC_OPCODE_STOP_2_PAR 493 /* last function with two or more parameters' OpCode + 1 */
#define SC_OPCODE_STOP_FUNCTION SC_OPCODE_STOP_2_PAR /* last function's OpCode + 1 */
#define SC_OPCODE_LAST_OPCODE_ID (SC_OPCODE_STOP_FUNCTION - 1) /* last OpCode */
diff --git a/include/formula/opcode.hxx b/include/formula/opcode.hxx
index facaaabfa89b..30dc94ff578c 100644
--- a/include/formula/opcode.hxx
+++ b/include/formula/opcode.hxx
@@ -284,6 +284,8 @@ enum OpCode : sal_uInt16
ocRawSubtract = SC_OPCODE_RAWSUBTRACT,
ocIfs_MS = SC_OPCODE_IFS_MS,
ocSwitch_MS = SC_OPCODE_SWITCH_MS,
+ ocMinIfs_MS = SC_OPCODE_MINIFS_MS,
+ ocMaxIfs_MS = SC_OPCODE_MAXIFS_MS,
// Database functions
ocDBSum = SC_OPCODE_DB_SUM,
ocDBCount = SC_OPCODE_DB_COUNT,
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
{