GNOME Bugzilla – Bug 586218
"IF" should be able to return ranges and empty values
Last modified: 2009-06-18 20:54:29 UTC
Please describe the problem: In excel this works: {=sum(if(find("VALUE",RANGE)=1,RANGE))} But I get an #VALUE in gnumeric Steps to reproduce: 1. 2. 3. Actual results: Expected results: Does this happen every time? Other information:
A1='0 A2='1 A10=sum(if(find("0",a1:a2)=1,a1:a2)) In Excel and Gnumeric I get #VALUE! because FIND returns that when it does not find the text. Wrap the find in iferror(...,0) or something like that. (I'm not sure when Gnumeric got the iferror function, though.)
Sorry I was just trying to find the simplest problem where the error accord. I was using iserror, which gnumeric supports, and I still get an #VALUE. I tried =sum(if(iserror(find("0",A1:A2)),B1:B2))
Please supply a complete test sheet that shows the problem, i.e., does things different than what Excel would do. Your formula works fine for me, except that it adds the entries for which is the no zero, not the ones with a zero. A "not" fixes that.
Created attachment 136936 [details] This works in excel and not gnumeric I don't know why excel displays 2 as the result, but at least it does not return #Value. But I have a document that's pretty complex, which I really need this.
There clearly is a difference between Gnumeric and Excel, but it will not matter to you. If you want implicit iteration (Gnumeric or Excel), you need to enter the formulas an an 1x1 array formula. To do that, press Ctrl-Shift-Enter instead of just Enter. Both Gnumeric and Excel then produce the result 1. Leaving open to determine why we differ in the non-array case.
That problem is in "if". The following patch (which applies to the 1.9.x series only) fixes it: diff --git a/src/func-builtin.c b/src/func-builtin.c index 7438bd7..e03caf5 100644 --- a/src/func-builtin.c +++ b/src/func-builtin.c @@ -331,11 +331,18 @@ gnumeric_if2 (GnmFuncEvalInfo *ei, int argc, GnmExprConstPtr const *argv) branch = value_get_as_bool (args[0], &err) ? 1 : 2; for (i = 1; i <= 2; i++) { - args[i] = argc > i - ? (branch == i ? - gnm_expr_eval (argv[branch], ei->pos, 0) - : value_new_empty ()) - : NULL; + GnmExprEvalFlags flags = + GNM_EXPR_EVAL_PERMIT_NON_SCALAR | + GNM_EXPR_EVAL_PERMIT_EMPTY; + + if (i >= argc) + args[i] = NULL; + else if (branch == i) { + args[i] = gnm_expr_eval (argv[branch], ei->pos, flags); + if (!args[i]) + args[i] = value_new_empty (); + } else + args[i] = value_new_empty (); } res = gnumeric_if (ei, (GnmValue const * const *)args);
This problem has been fixed in the development version. The fix will be available in the next major software release. Thank you for your bug report.