[gnumeric] Funcs: add SUMIFS and AVERAGEIFS
- From: Morten Welinder <mortenw src gnome org>
- To: commits-list gnome org
- Cc:
- Subject: [gnumeric] Funcs: add SUMIFS and AVERAGEIFS
- Date: Mon, 8 Aug 2016 00:53:32 +0000 (UTC)
commit d5471ea9bae92a959689b5abfa202b8205363d07
Author: Morten Welinder <terra gnome org>
Date: Sun Aug 7 20:52:58 2016 -0400
Funcs: add SUMIFS and AVERAGEIFS
NEWS | 2 +
doc/C/func.defs | 20 ++-
doc/C/functions.xml | 67 ++++++
plugins/fn-math/functions.c | 374 ++++++++++++++++++++------------
plugins/fn-math/plugin.xml.in | 2 +
plugins/openoffice/openoffice-read.c | 4 +-
plugins/openoffice/openoffice-write.c | 4 +-
src/value-sheet.c | 2 -
8 files changed, 328 insertions(+), 147 deletions(-)
---
diff --git a/NEWS b/NEWS
index 617081f..224da1a 100644
--- a/NEWS
+++ b/NEWS
@@ -12,6 +12,8 @@ Morten:
* New function TEXTJOIN.
* New function IFS.
* New function SWITCH.
+ * New function SUMIFS.
+ * New function AVERAGEIFS.
--------------------------------------------------------------------------
Gnumeric 1.12.31
diff --git a/doc/C/func.defs b/doc/C/func.defs
index 0eb3542..22ebf50 100644
--- a/doc/C/func.defs
+++ b/doc/C/func.defs
@@ -3309,6 +3309,15 @@ The depreciation coefficient used is:
@SEEALSO=SUMIF,COUNTIF
@CATEGORY=Mathematics
+@FUNCTION=AVERAGEIFS
+@SHORTDESC=average of the cells in @{actual_range} for which the corresponding cells in the range meet the
given criteria
+@SYNTAX=AVERAGEIFS(range1,criteria1,…)
+@ARGUMENTDESCRIPTION=@{range1}: cell area
+@{criteria1}: condition for a cell to be included
+@EXCEL=This function is Excel compatible.
+@SEEALSO=AVERAGE,AVERAGEIF
+
+@CATEGORY=Mathematics
@FUNCTION=BETA
@SHORTDESC=Euler beta function
@SYNTAX=BETA(x,y)
@@ -3961,7 +3970,16 @@ If @{d} is less than zero, @{x} is rounded away from 0 to the left of the decima
@{actual_range}: cell area, defaults to @{range}
@NOTE=If the @{actual_range} has a size that differs from the size of @{range}, @{actual_range} is resized
(retaining the top-left corner) to match the size of @{range}.
@EXCEL=This function is Excel compatible.
-@SEEALSO=SUM,COUNTIF
+@SEEALSO=SUM,SUMIFS,COUNTIF
+
+@CATEGORY=Mathematics
+@FUNCTION=SUMIFS
+@SHORTDESC=sum of the cells in @{actual_range} for which the corresponding cells in the range meet the given
criteria
+@SYNTAX=SUMIFS(range1,criteria1,…)
+@ARGUMENTDESCRIPTION=@{range1}: cell area
+@{criteria1}: condition for a cell to be included
+@EXCEL=This function is Excel compatible.
+@SEEALSO=SUM,SUMIF
@CATEGORY=Mathematics
@FUNCTION=SUMPRODUCT
diff --git a/doc/C/functions.xml b/doc/C/functions.xml
index f38ef22..c9e5f17 100644
--- a/doc/C/functions.xml
+++ b/doc/C/functions.xml
@@ -10747,6 +10747,39 @@
</para>
</refsect1>
</refentry>
+ <refentry id="gnumeric-function-AVERAGEIFS">
+ <refmeta>
+ <refentrytitle>
+ <function>AVERAGEIFS</function>
+ </refentrytitle>
+ </refmeta>
+ <refnamediv>
+ <refname>
+ <function>AVERAGEIFS</function>
+ </refname>
+ <refpurpose>
+ average of the cells in <parameter>actual_range</parameter> for which the corresponding cells in the
range meet the given criteria
+ </refpurpose>
+ </refnamediv>
+ <refsynopsisdiv>
+
<synopsis><function>AVERAGEIFS</function>(<parameter>range1</parameter>,<parameter>criteria1</parameter>,<parameter/>…)</synopsis>
+ </refsynopsisdiv>
+ <refsect1>
+ <title>Arguments</title>
+ <para><parameter>range1</parameter>: cell area</para>
+ <para><parameter>criteria1</parameter>: condition for a cell to be included</para>
+ </refsect1>
+ <refsect1>
+ <title>Microsoft Excel Compatibility</title>
+ <para>This function is Excel compatible.</para>
+ </refsect1>
+ <refsect1>
+ <title>See also</title>
+ <para><link linkend="gnumeric-function-AVERAGE"><function>AVERAGE</function></link>,
+ <link linkend="gnumeric-function-AVERAGEIF"><function>AVERAGEIF</function></link>.
+ </para>
+ </refsect1>
+ </refentry>
<refentry id="gnumeric-function-BETA">
<refmeta>
<refentrytitle>
@@ -13262,10 +13295,44 @@
<refsect1>
<title>See also</title>
<para><link linkend="gnumeric-function-SUM"><function>SUM</function></link>,
+ <link linkend="gnumeric-function-SUMIFS"><function>SUMIFS</function></link>,
<link linkend="gnumeric-function-COUNTIF"><function>COUNTIF</function></link>.
</para>
</refsect1>
</refentry>
+ <refentry id="gnumeric-function-SUMIFS">
+ <refmeta>
+ <refentrytitle>
+ <function>SUMIFS</function>
+ </refentrytitle>
+ </refmeta>
+ <refnamediv>
+ <refname>
+ <function>SUMIFS</function>
+ </refname>
+ <refpurpose>
+ sum of the cells in <parameter>actual_range</parameter> for which the corresponding cells in the
range meet the given criteria
+ </refpurpose>
+ </refnamediv>
+ <refsynopsisdiv>
+
<synopsis><function>SUMIFS</function>(<parameter>range1</parameter>,<parameter>criteria1</parameter>,<parameter/>…)</synopsis>
+ </refsynopsisdiv>
+ <refsect1>
+ <title>Arguments</title>
+ <para><parameter>range1</parameter>: cell area</para>
+ <para><parameter>criteria1</parameter>: condition for a cell to be included</para>
+ </refsect1>
+ <refsect1>
+ <title>Microsoft Excel Compatibility</title>
+ <para>This function is Excel compatible.</para>
+ </refsect1>
+ <refsect1>
+ <title>See also</title>
+ <para><link linkend="gnumeric-function-SUM"><function>SUM</function></link>,
+ <link linkend="gnumeric-function-SUMIF"><function>SUMIF</function></link>.
+ </para>
+ </refsect1>
+ </refentry>
<refentry id="gnumeric-function-SUMPRODUCT">
<refmeta>
<refentrytitle>
diff --git a/plugins/fn-math/functions.c b/plugins/fn-math/functions.c
index ddeef89..7076d9e 100644
--- a/plugins/fn-math/functions.c
+++ b/plugins/fn-math/functions.c
@@ -49,7 +49,6 @@
GNM_PLUGIN_MODULE_HEADER;
-
#define FUNCTION_A_DESC GNM_FUNC_HELP_DESCRIPTION, F_("Numbers, text and logical values are " \
"included in the calculation too. If the cell
contains text or " \
"the argument evaluates to FALSE, it is counted as
value zero (0). " \
@@ -57,6 +56,201 @@ GNM_PLUGIN_MODULE_HEADER;
/***************************************************************************/
+static GnmValue *
+ifs_func (GPtrArray *data, GPtrArray *crits, GnmValue const *vals,
+ float_range_function_t fun, GnmStdError err,
+ GnmEvalPos const *ep, CollectFlags flags)
+{
+ int sx, sy, x, y;
+ unsigned ui, N = 0, nalloc = 0;
+ gnm_float *xs = NULL;
+ GnmValue *res = NULL;
+ gnm_float fres;
+
+ g_return_val_if_fail (data->len == crits->len, NULL);
+
+ if (flags & ~(COLLECT_IGNORE_STRINGS |
+ COLLECT_IGNORE_BOOLS |
+ COLLECT_IGNORE_BLANKS |
+ COLLECT_IGNORE_ERRORS)) {
+ g_warning ("unsupported flags in ifs_func %x", flags);
+ }
+
+ sx = value_area_get_width (vals, ep);
+ sy = value_area_get_height (vals, ep);
+ for (ui = 0; ui < data->len; ui++) {
+ GnmValue const *datai = g_ptr_array_index (data, ui);
+ if (value_area_get_width (datai, ep) != sx ||
+ value_area_get_height (datai, ep) != sy)
+ return value_new_error_VALUE (ep);
+ }
+
+ for (y = 0; y < sy; y++) {
+ for (x = 0; x < sy; x++) {
+ GnmValue const *v;
+ gboolean match = TRUE;
+
+ for (ui = 0; match && ui < crits->len; ui++) {
+ GnmCriteria *crit = g_ptr_array_index (crits, ui);
+ GnmValue const *datai = g_ptr_array_index (data, ui);
+ v = value_area_get_x_y (datai, x, y, ep);
+
+ match = crit->fun (v, crit);
+ }
+ if (!match)
+ continue;
+
+ // Match. Maybe collect the data point.
+
+ v = value_area_get_x_y (vals, x, y, ep);
+ if ((flags & COLLECT_IGNORE_STRINGS) && VALUE_IS_STRING (v))
+ continue;
+ if ((flags & COLLECT_IGNORE_BOOLS) && VALUE_IS_BOOLEAN (v))
+ continue;
+ if ((flags & COLLECT_IGNORE_BLANKS) && VALUE_IS_EMPTY (v))
+ continue;
+ if ((flags & COLLECT_IGNORE_ERRORS) && VALUE_IS_ERROR (v))
+ continue;
+
+ if (VALUE_IS_ERROR (v)) {
+ res = value_dup (v);
+ goto out;
+ }
+
+ if (N >= nalloc) {
+ nalloc = (2 * nalloc) + 100;
+ xs = g_renew (gnm_float, xs, nalloc);
+ }
+ xs[N++] = value_get_as_float (v);
+ }
+ }
+
+ if (fun (xs, N, &fres)) {
+ res = value_new_error_std (ep, err);
+ } else
+ res = value_new_float (fres);
+
+out:
+ g_free (xs);
+ return res;
+}
+
+static GnmValue *
+oldstyle_if_func (GnmFuncEvalInfo *ei, GnmValue const * const *argv,
+ float_range_function_t fun, GnmStdError err)
+{
+ GPtrArray *crits = g_ptr_array_new_with_free_func ((GDestroyNotify)free_criteria);
+ GPtrArray *data = g_ptr_array_new ();
+ GODateConventions const *date_conv =
+ workbook_date_conv (ei->pos->sheet->workbook);
+ GnmValue *res;
+ gboolean insanity;
+ GnmValue const *vals;
+
+ g_ptr_array_add (data, (gpointer)(argv[0]));
+ g_ptr_array_add (crits, parse_criteria (argv[1], date_conv, TRUE));
+
+ if (argv[2]) {
+ vals = argv[2];
+ insanity = (value_area_get_width (vals, ei->pos) != value_area_get_width (argv[0], ei->pos) ||
+ value_area_get_height (vals, ei->pos) != value_area_get_height (argv[0],
ei->pos));
+ if (insanity) {
+ // The value area is the wrong size, but this function
+ // is *documented* to use an area of the right size
+ // with the same starting point. That's absolutely
+ // insane -- for starters, we are tracking the wrong
+ // dependents.
+
+ // For now, bail.
+ res = value_new_error_VALUE (ei->pos);
+ goto out;
+ }
+ } else {
+ vals = argv[0];
+ insanity = FALSE;
+ }
+
+ res = ifs_func (data, crits, vals,
+ fun, err, ei->pos,
+ COLLECT_IGNORE_STRINGS |
+ COLLECT_IGNORE_BLANKS |
+ COLLECT_IGNORE_BOOLS);
+
+out:
+ g_ptr_array_free (data, TRUE);
+ g_ptr_array_free (crits, TRUE);
+
+ return res;
+}
+
+static GnmValue *
+newstyle_if_func (GnmFuncEvalInfo *ei, int argc, GnmExprConstPtr const *argv,
+ float_range_function_t fun, GnmStdError err)
+{
+ GPtrArray *crits = g_ptr_array_new_with_free_func ((GDestroyNotify)free_criteria);
+ GPtrArray *data = g_ptr_array_new_with_free_func ((GDestroyNotify)value_release);
+ GODateConventions const *date_conv =
+ workbook_date_conv (ei->pos->sheet->workbook);
+ GnmValue *res;
+ GnmValue *vals = NULL;
+ int i;
+
+ if ((argc & 1) == 0) {
+ res = value_new_error_VALUE (ei->pos);
+ goto out;
+ }
+
+ vals = gnm_expr_eval (argv[0], ei->pos,
+ GNM_EXPR_EVAL_PERMIT_NON_SCALAR |
+ GNM_EXPR_EVAL_WANT_REF);
+ if (VALUE_IS_ERROR (vals)) {
+ res = value_dup (vals);
+ goto out;
+ }
+ if (!VALUE_IS_CELLRANGE (vals)) {
+ res = value_new_error_VALUE (ei->pos);
+ goto out;
+ }
+
+ for (i = 1; i + 1 < argc; i += 2) {
+ GnmValue *area, *crit;
+
+ area = gnm_expr_eval (argv[i], ei->pos,
+ GNM_EXPR_EVAL_PERMIT_NON_SCALAR |
+ GNM_EXPR_EVAL_WANT_REF);
+ if (VALUE_IS_ERROR (area)) {
+ res = area;
+ goto out;
+ }
+ g_ptr_array_add (data, area);
+
+ crit = gnm_expr_eval (argv[i + 1], ei->pos,
+ GNM_EXPR_EVAL_SCALAR_NON_EMPTY);
+ if (VALUE_IS_ERROR (crit)) {
+ res = crit;
+ goto out;
+ }
+
+ g_ptr_array_add (crits, parse_criteria (crit, date_conv, TRUE));
+ value_release (crit);
+ }
+
+ res = ifs_func (data, crits, vals,
+ fun, err, ei->pos,
+ COLLECT_IGNORE_STRINGS |
+ COLLECT_IGNORE_BLANKS |
+ COLLECT_IGNORE_BOOLS);
+
+out:
+ g_ptr_array_free (data, TRUE);
+ g_ptr_array_free (crits, TRUE);
+ value_release (vals);
+
+ return res;
+}
+
+/***************************************************************************/
+
static GnmFuncHelp const help_gcd[] = {
{ GNM_FUNC_HELP_NAME, F_("GCD:the greatest common divisor")},
{ GNM_FUNC_HELP_ARG, F_("n0:positive integer")},
@@ -578,108 +772,31 @@ static GnmFuncHelp const help_sumif[] = {
"is resized (retaining the top-left corner)"
" to match the size of @{range}.")},
{ GNM_FUNC_HELP_EXCEL, F_("This function is Excel compatible.") },
- { GNM_FUNC_HELP_SEEALSO, "SUM,COUNTIF"},
+ { GNM_FUNC_HELP_SEEALSO, "SUM,SUMIFS,COUNTIF"},
{ GNM_FUNC_HELP_END}
};
-typedef struct {
- GnmCriteria *crit;
- Sheet *target_sheet;
- int offset_col, offset_row;
- gnm_float sum;
- int count;
-} SumIfClosure;
-
static GnmValue *
-cb_sumif (GnmCellIter const *iter, SumIfClosure *res)
+gnumeric_sumif (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
{
- GnmCell *cell = iter->cell;
- GnmValue *v;
-
- if (cell) {
- gnm_cell_eval (cell);
- v = cell->value;
- } else
- v = value_new_empty (); /* Never released */
-
- if (!VALUE_IS_EMPTY (v) && !VALUE_IS_NUMBER (v) && !VALUE_IS_STRING (v))
- return NULL;
-
- if (!res->crit->fun (v, res->crit))
- return NULL;
-
- if (NULL != res->target_sheet) {
- GnmCell *cell = sheet_cell_get
- (res->target_sheet,
- iter->pp.eval.col + res->offset_col,
- iter->pp.eval.row + res->offset_row);
- if (!cell)
- return NULL;
-
- gnm_cell_eval (cell);
- v = cell->value;
- }
-
- if (!VALUE_IS_FLOAT (v))
- return NULL;
+ return oldstyle_if_func (ei, argv, gnm_range_sum, GNM_ERROR_DIV0);
+}
- res->sum += value_get_as_float (v);
- res->count++;
+/***************************************************************************/
- return NULL;
-}
+static GnmFuncHelp const help_sumifs[] = {
+ { GNM_FUNC_HELP_NAME, F_("SUMIFS:sum of the cells in @{actual_range} for which the corresponding
cells in the range meet the given criteria")},
+ { GNM_FUNC_HELP_ARG, F_("range1:cell area")},
+ { GNM_FUNC_HELP_ARG, F_("criteria1:condition for a cell to be included")},
+ { GNM_FUNC_HELP_EXCEL, F_("This function is Excel compatible.") },
+ { GNM_FUNC_HELP_SEEALSO, "SUM,SUMIF"},
+ { GNM_FUNC_HELP_END}
+};
static GnmValue *
-gnumeric_sumif (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
+gnumeric_sumifs (GnmFuncEvalInfo *ei, int argc, GnmExprConstPtr const *argv)
{
- GnmRange rs;
- Sheet *start_sheet, *end_sheet;
- SumIfClosure res;
- GnmValue *problem;
- GODateConventions const *date_conv =
- workbook_date_conv (ei->pos->sheet->workbook);
-
- /* XL has some limitations on @range that we currently emulate, but do
- * not need to.
- * 1) @range must be a range, arrays are not supported
- * 2) @range can not be 3d */
- if (!VALUE_IS_CELLRANGE (argv[0]) ||
- (!VALUE_IS_NUMBER (argv[1]) && !VALUE_IS_STRING (argv[1])) ||
- (argv[2] != NULL && !VALUE_IS_CELLRANGE (argv[2])))
- return value_new_error_VALUE (ei->pos);
-
- gnm_rangeref_normalize (&argv[0]->v_range.cell, ei->pos,
- &start_sheet, &end_sheet,
- &rs);
- if (start_sheet != end_sheet)
- return value_new_error_VALUE (ei->pos);
-
- if (argv[2]) {
- GnmRange ra;
- /* See 557782. */
- gnm_rangeref_normalize (&argv[2]->v_range.cell, ei->pos,
- &res.target_sheet, &end_sheet,
- &ra);
- if (res.target_sheet != end_sheet)
- return value_new_error_VALUE (ei->pos);
-
- res.offset_col = ra.start.col - rs.start.col;
- res.offset_row = ra.start.row - rs.start.row;
- } else
- res.target_sheet = NULL;
-
- res.sum = 0;
- res.count = 0;
- res.crit = parse_criteria (argv[1], date_conv, TRUE);
- problem = sheet_foreach_cell_in_range
- (start_sheet, res.crit->iter_flags,
- rs.start.col, rs.start.row, rs.end.col, rs.end.row,
- (CellIterFunc) &cb_sumif, &res);
- free_criteria (res.crit);
-
- if (NULL != problem)
- return value_new_error_VALUE (ei->pos);
- return value_new_float (res.sum);
+ return newstyle_if_func (ei, argc, argv, gnm_range_sum, GNM_ERROR_DIV0);
}
/***************************************************************************/
@@ -697,57 +814,26 @@ static GnmFuncHelp const help_averageif[] = {
static GnmValue *
gnumeric_averageif (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
{
- GnmRange rs;
- Sheet *start_sheet, *end_sheet;
- SumIfClosure res;
- GnmValue *problem;
- GODateConventions const *date_conv =
- workbook_date_conv (ei->pos->sheet->workbook);
-
- /* XL has some limitations on @range that we currently emulate, but do
- * not need to.
- * 1) @range must be a range, arrays are not supported
- * 2) @range can not be 3d */
- if (!VALUE_IS_CELLRANGE (argv[0]) ||
- (!VALUE_IS_NUMBER (argv[1]) && !VALUE_IS_STRING (argv[1])) ||
- (argv[2] != NULL && !VALUE_IS_CELLRANGE (argv[2])))
- return value_new_error_VALUE (ei->pos);
-
- gnm_rangeref_normalize (&argv[0]->v_range.cell, ei->pos,
- &start_sheet, &end_sheet,
- &rs);
- if (start_sheet != end_sheet)
- return value_new_error_VALUE (ei->pos);
+ return oldstyle_if_func (ei, argv, gnm_range_average, GNM_ERROR_DIV0);
+}
- if (argv[2]) {
- GnmRange ra;
- /* See 557782. */
- gnm_rangeref_normalize (&argv[2]->v_range.cell, ei->pos,
- &res.target_sheet, &end_sheet,
- &ra);
- if (res.target_sheet != end_sheet)
- return value_new_error_VALUE (ei->pos);
-
- res.offset_col = ra.start.col - rs.start.col;
- res.offset_row = ra.start.row - rs.start.row;
- } else
- res.target_sheet = NULL;
+/***************************************************************************/
- res.sum = 0.;
- res.count = 0;
- res.crit = parse_criteria (argv[1], date_conv, TRUE);
- problem = sheet_foreach_cell_in_range
- (start_sheet, res.crit->iter_flags,
- rs.start.col, rs.start.row, rs.end.col, rs.end.row,
- (CellIterFunc) &cb_sumif, &res);
- free_criteria (res.crit);
+static GnmFuncHelp const help_averageifs[] = {
+ { GNM_FUNC_HELP_NAME, F_("AVERAGEIFS:average of the cells in @{actual_range} for which the
corresponding cells in the range meet the given criteria")},
+ { GNM_FUNC_HELP_ARG, F_("range1:cell area")},
+ { GNM_FUNC_HELP_ARG, F_("criteria1:condition for a cell to be included")},
+ { GNM_FUNC_HELP_EXCEL, F_("This function is Excel compatible.") },
+ { GNM_FUNC_HELP_SEEALSO, "AVERAGE,AVERAGEIF"},
+ { GNM_FUNC_HELP_END}
+};
- if (NULL != problem)
- return value_new_error_VALUE (ei->pos);
- if (res.count == 0)
- return value_new_error_DIV0 (ei->pos);
- return value_new_float (res.sum/res.count);
+static GnmValue *
+gnumeric_averageifs (GnmFuncEvalInfo *ei, int argc, GnmExprConstPtr const *argv)
+{
+ return newstyle_if_func (ei, argc, argv, gnm_range_sum, GNM_ERROR_DIV0);
}
+
/***************************************************************************/
static GnmFuncHelp const help_ceiling[] = {
@@ -3533,9 +3619,17 @@ GnmFuncDescriptor const math_functions[] = {
{ "sumif", "rS|r", help_sumif,
gnumeric_sumif, NULL, NULL, NULL,
GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
+ { "sumifs", NULL, help_sumifs,
+ NULL, gnumeric_sumifs, NULL, NULL,
+ GNM_FUNC_SIMPLE,
+ GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_NO_TESTSUITE },
{ "averageif", "rS|r", help_averageif,
gnumeric_averageif, NULL, NULL, NULL,
GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_NO_TESTSUITE },
+ { "averageifs", NULL, help_averageifs,
+ NULL, gnumeric_averageifs, NULL, NULL,
+ GNM_FUNC_SIMPLE,
+ GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_NO_TESTSUITE },
{ "sumproduct", NULL, help_sumproduct,
NULL, gnumeric_sumproduct, NULL, NULL,
GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
diff --git a/plugins/fn-math/plugin.xml.in b/plugins/fn-math/plugin.xml.in
index 50f3075..20ad314 100644
--- a/plugins/fn-math/plugin.xml.in
+++ b/plugins/fn-math/plugin.xml.in
@@ -24,6 +24,7 @@
<function name="atan2"/>
<function name="atanh"/>
<function name="averageif"/>
+ <function name="averageifs"/>
<function name="beta"/>
<function name="betaln"/>
<function name="ceil"/>
@@ -94,6 +95,7 @@
<function name="sqrtpi"/>
<function name="suma"/>
<function name="sumif"/>
+ <function name="sumifs"/>
<function name="sumproduct"/>
<function name="sumsq"/>
<function name="sumx2my2"/>
diff --git a/plugins/openoffice/openoffice-read.c b/plugins/openoffice/openoffice-read.c
index bc73b13..fece921 100644
--- a/plugins/openoffice/openoffice-read.c
+++ b/plugins/openoffice/openoffice-read.c
@@ -12874,15 +12874,14 @@ oo_func_map_in (GnmConventions const *convs, Workbook *scope,
/* The following is a list of the functions defined in ODF OpenFormula draft 20090508 */
/* where we do not have a function with the same name */
- { "AVERAGEIFS","ODF.AVERAGEIFS" },
{ "COUNTIFS","ODF.COUNTIFS" },
{ "DDE","ODF.DDE" },
{ "MULTIPLE.OPERATIONS","ODF.MULTIPLE.OPERATIONS" },
- { "SUMIFS","ODF.SUMIFS" },
/* The following is a complete list of the functions defined in ODF OpenFormula draft 20090508 */
/* We should determine whether any mapping is needed. */
+ { "AVERAGEIFS","AVERAGEIFS" },
{ "B","BINOM.DIST.RANGE" },
{ "CEILING","ODF.CEILING" }, /* see handler */
{ "CHISQINV","R.QCHISQ" },
@@ -12949,6 +12948,7 @@ oo_func_map_in (GnmConventions const *convs, Workbook *scope,
{ "RANK.AVG","RANK.AVG" },
{ "STDEV.S","STDEV" },
{ "STDEV.P","STDEVP" },
+ { "SUMIFS", "SUMIFS" },
{ "SWITCH", "SWITCH" },
{ "T.INV","R.QT" },
{ "T.INV.2T","TINV" },
diff --git a/plugins/openoffice/openoffice-write.c b/plugins/openoffice/openoffice-write.c
index e1408dc..e74a5da 100644
--- a/plugins/openoffice/openoffice-write.c
+++ b/plugins/openoffice/openoffice-write.c
@@ -2266,7 +2266,7 @@ odf_expr_func_handler (GnmConventionsOut *out, GnmExprFunction const *func)
{ "AVERAGE","AVERAGE" },
{ "AVERAGEA","AVERAGEA" },
{ "AVERAGEIF","AVERAGEIF" },
- /* { "ODF.AVERAGEIFS","AVERAGEIFS" }, not implemented */
+ { "AVERAGEIFS","AVERAGEIFS" },
{ "BINOM.DIST.RANGE","B" },
{ "BASE","BASE" },
{ "BESSELI","BESSELI" },
@@ -2592,7 +2592,7 @@ odf_expr_func_handler (GnmConventionsOut *out, GnmExprFunction const *func)
{ "SUBTOTAL","SUBTOTAL" },
{ "SUM","SUM" },
{ "SUMIF","SUMIF" },
- /* { "SUMIFS","SUMIFS" }, not implemented */
+ { "SUMIFS","SUMIFS" },
{ "ODF.SUMPRODUCT","SUMPRODUCT" },
{ "SUMSQ","SUMSQ" },
{ "SUMX2MY2","SUMX2MY2" },
diff --git a/src/value-sheet.c b/src/value-sheet.c
index 474f2ad..051378e 100644
--- a/src/value-sheet.c
+++ b/src/value-sheet.c
@@ -192,8 +192,6 @@ value_area_get_x_y (GnmValue const *v, int x, int y, GnmEvalPos const *ep)
return value_new_empty ();
} else
return v;
-
- return NULL;
}
typedef struct {
[
Date Prev][
Date Next] [
Thread Prev][
Thread Next]
[
Thread Index]
[
Date Index]
[
Author Index]
