After an evaluation, GNOME has moved from Bugzilla to GitLab. Learn more about GitLab.
No new issues can be reported in GNOME Bugzilla anymore.
To report an issue in a GNOME project, go to GNOME GitLab.
Do not go to GNOME Gitlab for: Bluefish, Doxygen, GnuCash, GStreamer, java-gnome, LDTP, NetworkManager, Tomboy.
Bug 586218 - "IF" should be able to return ranges and empty values
"IF" should be able to return ranges and empty values
Status: RESOLVED FIXED
Product: Gnumeric
Classification: Applications
Component: General
git master
Other All
: Normal normal
: ---
Assigned To: Jody Goldberg
Jody Goldberg
Depends on:
Blocks:
 
 
Reported: 2009-06-18 09:09 UTC by Ólafur Arason
Modified: 2009-06-18 20:54 UTC
See Also:
GNOME target: ---
GNOME version: ---


Attachments
This works in excel and not gnumeric (13.50 KB, application/vnd.ms-excel)
2009-06-18 19:04 UTC, Ólafur Arason
Details

Description Ólafur Arason 2009-06-18 09:09:08 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:
Comment 1 Morten Welinder 2009-06-18 14:00:51 UTC
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.)
Comment 2 Ólafur Arason 2009-06-18 17:50:21 UTC
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))
Comment 3 Morten Welinder 2009-06-18 18:38:52 UTC
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.

Comment 4 Ólafur Arason 2009-06-18 19:04:20 UTC
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.
Comment 5 Morten Welinder 2009-06-18 19:12:50 UTC
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.
Comment 6 Morten Welinder 2009-06-18 19:58:16 UTC
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);
Comment 7 Morten Welinder 2009-06-18 20:54:29 UTC
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.