[gnumeric] Funcs: add COUNTIFS and fix COUNTIF.
- From: Morten Welinder <mortenw src gnome org>
- To: commits-list gnome org
- Cc:
- Subject: [gnumeric] Funcs: add COUNTIFS and fix COUNTIF.
- Date: Mon, 8 Aug 2016 17:44:35 +0000 (UTC)
commit cec403d756c1b848e348a9804772bc2a55effaf2
Author: Morten Welinder <terra gnome org>
Date: Mon Aug 8 13:43:52 2016 -0400
Funcs: add COUNTIFS and fix COUNTIF.
ChangeLog | 3 +
NEWS | 12 +--
doc/C/func.defs | 9 ++
doc/C/functions.xml | 33 ++++++
plugins/fn-math/functions.c | 240 +++++++++++++---------------------------
plugins/fn-math/plugin.xml.in | 1 +
samples/excel12/countif.xlsx | Bin 12921 -> 13231 bytes
samples/excel12/ifs-funcs.xlsx | Bin 8878 -> 8850 bytes
src/criteria.c | 103 ++++++++++++++++--
src/criteria.h | 5 +
10 files changed, 228 insertions(+), 178 deletions(-)
---
diff --git a/ChangeLog b/ChangeLog
index 2d10abf..11e47ee 100644
--- a/ChangeLog
+++ b/ChangeLog
@@ -1,5 +1,8 @@
2016-08-08 Morten Welinder <terra gnome org>
+ * src/criteria.c (criteria_inspect_values): Add flag for string
+ coercion. Only the equality test wants that.
+
* src/value.c (criteria_inspect_values): Floats don't match
errors.
diff --git a/NEWS b/NEWS
index 60240cc..2483ee3 100644
--- a/NEWS
+++ b/NEWS
@@ -8,16 +8,12 @@ Jean:
Morten:
* Avoid gnome-common dependency.
- * New function CONCAT.
- * New function TEXTJOIN.
- * New function IFS.
- * New function SWITCH.
- * New function SUMIFS.
- * New function AVERAGEIFS.
- * New function MINIFS.
- * New function MAXIFS.
+ * New text functions CONCAT, TEXTJOIN.
+ * New selection functions IFS, SWITCH.
+ * New aggregation functions SUMIFS, AVERAGEIFS, MINIFS, MAXIFS, COUNTIFS.
* Fix criteria function issue with errors in the selector area.
* Fix corner case for MINA and MAXA.
+ * Fix criteria matching of numbers against strings.
--------------------------------------------------------------------------
Gnumeric 1.12.31
diff --git a/doc/C/func.defs b/doc/C/func.defs
index 5c2eff2..70267e5 100644
--- a/doc/C/func.defs
+++ b/doc/C/func.defs
@@ -3441,6 +3441,15 @@ The depreciation coefficient used is:
@SEEALSO=COUNT,SUMIF
@CATEGORY=Mathematics
+@FUNCTION=COUNTIFS
+@SHORTDESC=count of the cells meeting the given @{criteria}
+@SYNTAX=COUNTIFS(range,criteria,…)
+@ARGUMENTDESCRIPTION=@{range}: cell area
+@{criteria}: condition for a cell to be counted
+@EXCEL=This function is Excel compatible.
+@SEEALSO=COUNT,SUMIF
+
+@CATEGORY=Mathematics
@FUNCTION=CSC
@SHORTDESC=the cosecant of @{x}
@SYNTAX=CSC(x)
diff --git a/doc/C/functions.xml b/doc/C/functions.xml
index 92ff0d4..6f44e4a 100644
--- a/doc/C/functions.xml
+++ b/doc/C/functions.xml
@@ -11261,6 +11261,39 @@
</para>
</refsect1>
</refentry>
+ <refentry id="gnumeric-function-COUNTIFS">
+ <refmeta>
+ <refentrytitle>
+ <function>COUNTIFS</function>
+ </refentrytitle>
+ </refmeta>
+ <refnamediv>
+ <refname>
+ <function>COUNTIFS</function>
+ </refname>
+ <refpurpose>
+ count of the cells meeting the given <parameter>criteria</parameter>
+ </refpurpose>
+ </refnamediv>
+ <refsynopsisdiv>
+
<synopsis><function>COUNTIFS</function>(<parameter>range</parameter>,<parameter>criteria</parameter>,<parameter/>…)</synopsis>
+ </refsynopsisdiv>
+ <refsect1>
+ <title>Arguments</title>
+ <para><parameter>range</parameter>: cell area</para>
+ <para><parameter>criteria</parameter>: condition for a cell to be counted</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-COUNT"><function>COUNT</function></link>,
+ <link linkend="gnumeric-function-SUMIF"><function>SUMIF</function></link>.
+ </para>
+ </refsect1>
+ </refentry>
<refentry id="gnumeric-function-CSC">
<refmeta>
<refentrytitle>
diff --git a/plugins/fn-math/functions.c b/plugins/fn-math/functions.c
index 3ae56cc..56b81c1 100644
--- a/plugins/fn-math/functions.c
+++ b/plugins/fn-math/functions.c
@@ -58,87 +58,9 @@ 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)
+ float_range_function_t fun, GnmStdError err,
+ CollectFlags flags)
{
GPtrArray *crits = g_ptr_array_new_with_free_func ((GDestroyNotify)free_criteria);
GPtrArray *data = g_ptr_array_new ();
@@ -171,11 +93,9 @@ oldstyle_if_func (GnmFuncEvalInfo *ei, GnmValue const * const *argv,
insanity = FALSE;
}
- res = ifs_func (data, crits, vals,
- fun, err, ei->pos,
- COLLECT_IGNORE_STRINGS |
- COLLECT_IGNORE_BLANKS |
- COLLECT_IGNORE_BOOLS);
+ res = gnm_ifs_func (data, crits, vals,
+ fun, err, ei->pos,
+ flags);
out:
g_ptr_array_free (data, TRUE);
@@ -186,7 +106,8 @@ out:
static GnmValue *
newstyle_if_func (GnmFuncEvalInfo *ei, int argc, GnmExprConstPtr const *argv,
- float_range_function_t fun, GnmStdError err)
+ float_range_function_t fun, GnmStdError err,
+ gboolean no_data)
{
GPtrArray *crits = g_ptr_array_new_with_free_func ((GDestroyNotify)free_criteria);
GPtrArray *data = g_ptr_array_new_with_free_func ((GDestroyNotify)value_release);
@@ -195,25 +116,28 @@ newstyle_if_func (GnmFuncEvalInfo *ei, int argc, GnmExprConstPtr const *argv,
GnmValue *res;
GnmValue *vals = NULL;
int i;
+ int cstart = no_data ? 0 : 1;
- if ((argc & 1) == 0) {
+ if ((argc - cstart) & 1) {
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;
+ if (!no_data) {
+ 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) {
+ for (i = cstart; i + 1 < argc; i += 2) {
GnmValue *area, *crit;
area = gnm_expr_eval (argv[i], ei->pos,
@@ -223,6 +147,8 @@ newstyle_if_func (GnmFuncEvalInfo *ei, int argc, GnmExprConstPtr const *argv,
res = area;
goto out;
}
+ if (no_data && !vals)
+ vals = value_dup (area);
g_ptr_array_add (data, area);
crit = gnm_expr_eval (argv[i + 1], ei->pos,
@@ -236,11 +162,19 @@ newstyle_if_func (GnmFuncEvalInfo *ei, int argc, GnmExprConstPtr const *argv,
value_release (crit);
}
- res = ifs_func (data, crits, vals,
- fun, err, ei->pos,
- COLLECT_IGNORE_STRINGS |
- COLLECT_IGNORE_BLANKS |
- COLLECT_IGNORE_BOOLS);
+ if (!vals) {
+ // COUNTIFS with no arguments.
+ res = value_new_error_VALUE (ei->pos);
+ goto out;
+ }
+
+ res = gnm_ifs_func (data, crits, vals,
+ fun, err, ei->pos,
+ (no_data
+ ? 0
+ : COLLECT_IGNORE_STRINGS |
+ COLLECT_IGNORE_BLANKS |
+ COLLECT_IGNORE_BOOLS));
out:
g_ptr_array_free (data, TRUE);
@@ -698,66 +632,36 @@ static GnmFuncHelp const help_countif[] = {
{ GNM_FUNC_HELP_END}
};
-typedef struct {
- GnmCriteria *crit;
- int count;
-} CountIfClosure;
-
static GnmValue *
-cb_countif (GnmCellIter const *iter, CountIfClosure *res)
+gnumeric_countif (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 */
+ GnmValue const * argv3[3];
- if (!VALUE_IS_EMPTY (v) && !VALUE_IS_NUMBER (v) && !VALUE_IS_STRING (v))
- return NULL;
+ argv3[0] = argv[0];
+ argv3[1] = argv[1];
+ argv3[2] = NULL;
- if (!res->crit->fun (v, res->crit))
- return NULL;
+ return oldstyle_if_func (ei, argv3, gnm_range_count, GNM_ERROR_DIV0,
+ 0);
+}
- res->count++;
+/***************************************************************************/
- return NULL;
-}
+static GnmFuncHelp const help_countifs[] = {
+ { GNM_FUNC_HELP_NAME, F_("COUNTIFS:count of the cells meeting the given @{criteria}")},
+ { GNM_FUNC_HELP_ARG, F_("range:cell area")},
+ { GNM_FUNC_HELP_ARG, F_("criteria:condition for a cell to be counted")},
+ { GNM_FUNC_HELP_EXCEL, F_("This function is Excel compatible.") },
+ { GNM_FUNC_HELP_SEEALSO, "COUNT,SUMIF"},
+ { GNM_FUNC_HELP_END}
+};
static GnmValue *
-gnumeric_countif (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
+gnumeric_countifs (GnmFuncEvalInfo *ei, int argc, GnmExprConstPtr const *argv)
{
- GnmValueRange const *r = &argv[0]->v_range;
- Sheet *sheet;
- GnmValue *problem;
- CountIfClosure res;
- 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]) ||
- ((sheet = eval_sheet (r->cell.a.sheet, ei->pos->sheet)) != r->cell.b.sheet &&
- r->cell.b.sheet != NULL) ||
- (!VALUE_IS_NUMBER (argv[1]) && !VALUE_IS_STRING (argv[1])))
- return value_new_error_VALUE (ei->pos);
-
- res.count = 0;
- res.crit = parse_criteria (argv[1], date_conv, TRUE);
- problem = sheet_foreach_cell_in_range
- (sheet, res.crit->iter_flags,
- r->cell.a.col, r->cell.a.row, r->cell.b.col, r->cell.b.row,
- (CellIterFunc) &cb_countif, &res);
- free_criteria (res.crit);
-
- if (NULL != problem)
- return value_new_error_VALUE (ei->pos);
-
- return value_new_int (res.count);
+ return newstyle_if_func (ei, argc, argv,
+ gnm_range_count, GNM_ERROR_DIV0,
+ TRUE);
}
/***************************************************************************/
@@ -780,7 +684,10 @@ static GnmFuncHelp const help_sumif[] = {
static GnmValue *
gnumeric_sumif (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
{
- return oldstyle_if_func (ei, argv, gnm_range_sum, GNM_ERROR_DIV0);
+ return oldstyle_if_func (ei, argv, gnm_range_sum, GNM_ERROR_DIV0,
+ COLLECT_IGNORE_STRINGS |
+ COLLECT_IGNORE_BLANKS |
+ COLLECT_IGNORE_BOOLS);
}
/***************************************************************************/
@@ -798,7 +705,9 @@ static GnmFuncHelp const help_sumifs[] = {
static GnmValue *
gnumeric_sumifs (GnmFuncEvalInfo *ei, int argc, GnmExprConstPtr const *argv)
{
- return newstyle_if_func (ei, argc, argv, gnm_range_sum, GNM_ERROR_DIV0);
+ return newstyle_if_func (ei, argc, argv,
+ gnm_range_sum, GNM_ERROR_DIV0,
+ FALSE);
}
/***************************************************************************/
@@ -816,7 +725,10 @@ static GnmFuncHelp const help_averageif[] = {
static GnmValue *
gnumeric_averageif (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
{
- return oldstyle_if_func (ei, argv, gnm_range_average, GNM_ERROR_DIV0);
+ return oldstyle_if_func (ei, argv, gnm_range_average, GNM_ERROR_DIV0,
+ COLLECT_IGNORE_STRINGS |
+ COLLECT_IGNORE_BLANKS |
+ COLLECT_IGNORE_BOOLS);
}
/***************************************************************************/
@@ -835,7 +747,8 @@ static GnmValue *
gnumeric_averageifs (GnmFuncEvalInfo *ei, int argc, GnmExprConstPtr const *argv)
{
return newstyle_if_func (ei, argc, argv,
- gnm_range_average, GNM_ERROR_DIV0);
+ gnm_range_average, GNM_ERROR_DIV0,
+ FALSE);
}
/***************************************************************************/
@@ -854,7 +767,8 @@ static GnmValue *
gnumeric_minifs (GnmFuncEvalInfo *ei, int argc, GnmExprConstPtr const *argv)
{
return newstyle_if_func (ei, argc, argv,
- gnm_range_min, GNM_ERROR_DIV0);
+ gnm_range_min, GNM_ERROR_DIV0,
+ FALSE);
}
/***************************************************************************/
@@ -873,7 +787,8 @@ static GnmValue *
gnumeric_maxifs (GnmFuncEvalInfo *ei, int argc, GnmExprConstPtr const *argv)
{
return newstyle_if_func (ei, argc, argv,
- gnm_range_max, GNM_ERROR_DIV0);
+ gnm_range_max, GNM_ERROR_DIV0,
+ FALSE);
}
/***************************************************************************/
@@ -3490,10 +3405,13 @@ GnmFuncDescriptor const math_functions[] = {
gnumeric_coth, NULL, NULL, NULL,
GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_NO_TESTSUITE },
-/* MS Excel puts this in statistical */
{ "countif", "rS", help_countif,
gnumeric_countif, NULL, NULL, NULL,
GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
+ { "countifs", NULL, help_countifs,
+ NULL, gnumeric_countifs, NULL, NULL,
+ GNM_FUNC_SIMPLE,
+ GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_NO_TESTSUITE },
{ "ceil", "f", help_ceil,
gnumeric_ceil, NULL, NULL, NULL,
diff --git a/plugins/fn-math/plugin.xml.in b/plugins/fn-math/plugin.xml.in
index 90089c9..9dccaef 100644
--- a/plugins/fn-math/plugin.xml.in
+++ b/plugins/fn-math/plugin.xml.in
@@ -39,6 +39,7 @@
<function name="coth"/>
<function name="cotpi"/>
<function name="countif"/>
+ <function name="countifs"/>
<function name="csc"/>
<function name="csch"/>
<function name="degrees"/>
diff --git a/samples/excel12/countif.xlsx b/samples/excel12/countif.xlsx
index abbc254..aac753c 100644
Binary files a/samples/excel12/countif.xlsx and b/samples/excel12/countif.xlsx differ
diff --git a/samples/excel12/ifs-funcs.xlsx b/samples/excel12/ifs-funcs.xlsx
index 083021c..7488efa 100644
Binary files a/samples/excel12/ifs-funcs.xlsx and b/samples/excel12/ifs-funcs.xlsx differ
diff --git a/src/criteria.c b/src/criteria.c
index 0a8acaf..e4d600f 100644
--- a/src/criteria.c
+++ b/src/criteria.c
@@ -4,9 +4,8 @@ typedef enum { CRIT_NULL, CRIT_FLOAT, CRIT_WRONGTYPE, CRIT_STRING } CritType;
static CritType
criteria_inspect_values (GnmValue const *x, gnm_float *xr, gnm_float *yr,
- GnmCriteria *crit)
+ GnmCriteria *crit, gboolean coerce_to_float)
{
- GnmValue *vx;
GnmValue const *y = crit->x;
if (x == NULL || y == NULL)
@@ -34,7 +33,8 @@ criteria_inspect_values (GnmValue const *x, gnm_float *xr, gnm_float *yr,
g_warning ("This should not happen. Please report.");
return CRIT_WRONGTYPE;
- case VALUE_FLOAT:
+ case VALUE_FLOAT: {
+ GnmValue *vx;
*yr = value_get_as_float (y);
if (VALUE_IS_BOOLEAN (x) || VALUE_IS_ERROR (x))
@@ -44,6 +44,9 @@ criteria_inspect_values (GnmValue const *x, gnm_float *xr, gnm_float *yr,
return CRIT_FLOAT;
}
+ if (!coerce_to_float)
+ return CRIT_WRONGTYPE;
+
vx = format_match (value_peek_string (x), NULL, crit->date_conv);
if (VALUE_IS_EMPTY (vx) ||
VALUE_IS_BOOLEAN (y) != VALUE_IS_BOOLEAN (vx)) {
@@ -55,6 +58,7 @@ criteria_inspect_values (GnmValue const *x, gnm_float *xr, gnm_float *yr,
value_release (vx);
return CRIT_FLOAT;
}
+ }
}
@@ -64,7 +68,7 @@ criteria_test_equal (GnmValue const *x, GnmCriteria *crit)
gnm_float xf, yf;
GnmValue const *y = crit->x;
- switch (criteria_inspect_values (x, &xf, &yf, crit)) {
+ switch (criteria_inspect_values (x, &xf, &yf, crit, TRUE)) {
default:
g_assert_not_reached ();
case CRIT_NULL:
@@ -84,7 +88,7 @@ criteria_test_unequal (GnmValue const *x, GnmCriteria *crit)
{
gnm_float xf, yf;
- switch (criteria_inspect_values (x, &xf, &yf, crit)) {
+ switch (criteria_inspect_values (x, &xf, &yf, crit, FALSE)) {
default:
g_assert_not_reached ();
case CRIT_NULL:
@@ -105,7 +109,7 @@ criteria_test_less (GnmValue const *x, GnmCriteria *crit)
gnm_float xf, yf;
GnmValue const *y = crit->x;
- switch (criteria_inspect_values (x, &xf, &yf, crit)) {
+ switch (criteria_inspect_values (x, &xf, &yf, crit, FALSE)) {
default:
g_assert_not_reached ();
case CRIT_NULL:
@@ -125,7 +129,7 @@ criteria_test_greater (GnmValue const *x, GnmCriteria *crit)
gnm_float xf, yf;
GnmValue const *y = crit->x;
- switch (criteria_inspect_values (x, &xf, &yf, crit)) {
+ switch (criteria_inspect_values (x, &xf, &yf, crit, FALSE)) {
default:
g_assert_not_reached ();
case CRIT_NULL:
@@ -145,7 +149,7 @@ criteria_test_less_or_equal (GnmValue const *x, GnmCriteria *crit)
gnm_float xf, yf;
GnmValue const *y = crit->x;
- switch (criteria_inspect_values (x, &xf, &yf, crit)) {
+ switch (criteria_inspect_values (x, &xf, &yf, crit, FALSE)) {
default:
g_assert_not_reached ();
case CRIT_NULL:
@@ -165,7 +169,7 @@ criteria_test_greater_or_equal (GnmValue const *x, GnmCriteria *crit)
gnm_float xf, yf;
GnmValue const *y = crit->x;
- switch (criteria_inspect_values (x, &xf, &yf, crit)) {
+ switch (criteria_inspect_values (x, &xf, &yf, crit, FALSE)) {
default:
g_assert_not_reached ();
case CRIT_NULL:
@@ -569,3 +573,84 @@ filter_row:
}
/****************************************************************************/
+
+GnmValue *
+gnm_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 gnm_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 < sx; 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;
+}
+
+/****************************************************************************/
diff --git a/src/criteria.h b/src/criteria.h
index caab0ae..9589158 100644
--- a/src/criteria.h
+++ b/src/criteria.h
@@ -9,6 +9,7 @@
#include <cell.h>
#include <gutils.h>
#include <workbook.h>
+#include <collect.h>
#include <goffice/goffice.h>
#include <string.h>
@@ -48,6 +49,10 @@ GSList *parse_database_criteria (GnmEvalPos const *ep,
int find_column_of_field (GnmEvalPos const *ep,
GnmValue const *database, GnmValue const *field);
+GnmValue *gnm_ifs_func (GPtrArray *data, GPtrArray *crits, GnmValue const *vals,
+ float_range_function_t fun, GnmStdError err,
+ GnmEvalPos const *ep, CollectFlags flags);
+
G_END_DECLS
[
Date Prev][
Date Next] [
Thread Prev][
Thread Next]
[
Thread Index]
[
Date Index]
[
Author Index]
