Issue 4070 - IF() and CHOOSE() in array/matrix formulas (was: matrix formula in sum doesn't work)
Summary: IF() and CHOOSE() in array/matrix formulas (was: matrix formula in sum doesn...
Alias: None
Product: Calc
Classification: Application
Component: code (show other issues)
Version: 641
Hardware: All All
: P3 Trivial with 1 vote (vote)
Target Milestone: ---
Assignee: frank
QA Contact: issues@sc
: 4265 5857 6334 11731 16989 25898 31256 (view as issue list)
Depends on:
Blocks: 20494
  Show dependency tree
Reported: 2002-04-16 09:27 UTC by Unknown
Modified: 2013-08-07 15:15 UTC (History)
4 users (show)

See Also:
Latest Confirmation in: ---
Developer Difficulty: ---

example for the comment by mikedonovan (22.50 KB, application/octet-stream)
2003-07-20 13:38 UTC, Unknown
no flags Details

Note You need to log in before you can comment on or make changes to this issue.
Description Unknown 2002-04-16 09:27:22 UTC
A matrix formula of the form:


doesn't work.  I expect the above to sum the elements in D3:D20 only if the 
corresponding element in C equals 'T'.  Instead, the symbols "###" are 
displayed in the field, and the error message that appears in the status bar 
is "Error in parameter list."
Comment 1 peter.junge 2002-04-16 09:33:51 UTC
I'll have a look, Peter
Comment 2 peter.junge 2002-04-16 14:14:41 UTC
Hi Steven,
you can use SUMIF for this case.

Hi Falko,
these kind of constant arrays are not implemented. I would call this a
request for enhancement.

Best regards, Peter
Comment 3 Unknown 2002-04-17 10:33:43 UTC
I put the issue in the form compatible with SUMIF only for 
simplicity's sake.  In my case, I was trying to examine the values in 
two columns and as far as I know that cannot be done with SUMIF, eg:


Note that the OpenOffice documentation does state that this form of 
SUM should work, so this is definitely a documented feature that does 
not work.  The easiest but least satisfying approach would be to 
change the docs so they don't say this can be done.  IMO, the best 
approach would be to implement or fix the documented feature.  The 
latter approach is what I would like, since from a needed-feature 
perspective I'll have no option but to revert to M$ Excel without 
this capability.
Comment 4 ooo 2002-04-17 11:37:39 UTC
IF() or CHOSE() in a matrix/array formula doesn't work. It's simply
not implemented, and to do so would require a lot of changes in the
internal formula compiler and interpreter. Though it's on the mental
TODO list. AFAIK it's also not documented anywhere that it should

A workaround for the concrete problem would be an intermediate column
(say E3:E20) containing the array formula


and the final formula reading

Comment 5 Unknown 2002-04-17 13:00:09 UTC
If you look in the documentation for SUM, you will see the following 
text indicating that IF inside SUM  as a matrix/array formula should 


Now enter the following formula as a matrix formula:
In order to enter this as a matrix formula you must press the Shift + 
Ctrl + Enter key instead of simply pressing the Enter key to close 
the formula. The formula will then be shown in the Formula bar 
enclosed in braces.


If the feature cannot be changed, I'm sorry to hear that but I 
understand.  I thought of the workaround you proposed; it might 
suffice, esp if the column were in another worksheet so as not to 
mess with the appearance, but I must say I'd far rather have the 
matrix formula solution.  At a minimum, however, the docs should be 
updated so that they do not suggest the matrix formula approach will 
Comment 6 ooo 2002-04-18 09:57:28 UTC
er->thoth: Please read your quote again. There's no IF() mentioned,
neither in the formula nor in the describing text. The example given
does work.
Comment 7 Unknown 2002-04-20 03:53:43 UTC
True.... My apologies.  I assumed that a matrix formula was a matrix 
formula; I didn't realize the IF made it fundamentally different.  I 
now understand your judgment that this is a feature request and not a 
Comment 8 frank 2002-04-26 11:08:24 UTC
*** Issue 4265 has been marked as a duplicate of this issue. ***
Comment 9 Unknown 2002-05-16 13:21:15 UTC
Wait a moment...
Until you claim to be able to open an XLS file, you have to treat 
this as a bug, not a RFE...It is true, the documententation says 
nothing about using other functions inside a matrix formula (nor IF, 
nor others) but:

1) it not even says it's illegal. Hiding himself under the 
documentation is in perfect MS style :-)
2) it says I am able to open an Excel file, but in fact I'm not.

So, I vote for consider this issue a defect.
Comment 10 peter.junge 2002-05-21 09:09:04 UTC
Hi Roberto,
calling this a bug or not doesn't matter that much. But the problem
will not be resolved by voting for one of both alternatives ;-). Due
to the fact that the function is not implemented the appropriate
process for fixing is called 'enhancement'. Or, you can volunteer
fixing it yourself. Then you can of course flag the issue in a way you

Best regards, Peter
Comment 11 frank 2002-06-17 08:27:30 UTC
*** Issue 5857 has been marked as a duplicate of this issue. ***
Comment 12 Unknown 2002-08-06 13:56:22 UTC
Any idea about the planned timing of this enhancement?
I'm just waiting for this for dropping MS-Office in the recycle 

Best regards, Rob
Comment 13 Unknown 2003-01-02 12:30:03 UTC
Hi guys. I received a mail saying that the issue 4265 (a duplicate 
of this issue) has been changed status from RESOLVED to CLOSED. 
Wow...:-)) But, after downloading and installing the latest stable 
build, I found the same problem still unresolved. In fact this issue 
seems to be in status NEW with low priority...:-(
What does this fact mean? (Perhaps this is not the right place for 
this kind of comments, be patient...) Does it mean I can start to 
hope to see this "enhancement" implemented in the short?...

Many thanx in advance
Comment 14 nis 2003-02-04 15:32:01 UTC
Just for the record:


can be implemented using the same technique which is used in the 


X, Y or Z can be matrix references.

Perhaps this helps someone to get rid of MSO?

Comment 15 frank 2003-02-22 10:09:45 UTC
*** Issue 11731 has been marked as a duplicate of this issue. ***
Comment 16 ooo 2003-07-01 16:25:23 UTC
This is mine..
Comment 17 ooo 2003-07-01 16:27:13 UTC
Comment 18 oc 2003-07-17 11:29:54 UTC
*** Issue 16989 has been marked as a duplicate of this issue. ***
Comment 19 Unknown 2003-07-20 13:36:52 UTC
1.1 RC
first of all please open the file I have attached

I would like to explain which is (according to me) the true problem I 
pertain the conditional formulas:

According to me the problem is not the impossibility to use the 
function IF inside a formula of SUM in matrix: in fact the syntax 
advised from the  "openoffice" guide to the argument SUM resolves 
perfectly the problem, but ONLY for the function "Sum" (even if then 
the formula as introduced does not work in Excel, in reality is 
necessary to change the formula and to press again 
besides some problems can be resolved using automatic filters 
and "subtotal" (see the guide)

the TRUE problem is that is impossible to apply a lot of functions , 
as MODE, MEDIAN, MAX, MIN, GEOMAN, BIG, SMALL... to a part of data, 
in function of certain conditions, because these functions do not 
base themselves on sums; this problem make openoffice not useable in 
professional and scientific field till today 

:::::: EXAMPLE 

suppose that you examin a group of 2500 persons:

profession  - age - name - gender -  annual income

suppose to have to find the smaller income, and then the geometrical 
average of the income, but ONLY of the male of age included between 
20 and 30 years: does not exist any formula that include the function 
SUM to find this values; 
making a lot of other types of elaborations from such kind of data-
set is clearly impossible 


the most important thing is that openoffice in a lot of cases 
performs a conditional calculation (created with the syntax explained 
for the SUM) but performs it in the wrong manner, WITHOUT GIVING ANY 
WARNING: see for example the cells of the examples in the final part 
of the attached file, doing the comparison with the column of the 
exact results, calculated in MS Excel 
this is a serious bug, that should at least introduced in the
well-known problems, and an error message should be shown in the 


Compose different formulas constituted by functions SUM allows only 
to resolve some functions, but only if they are...
- based on combinations of SUM 
- based on algebric, logaritmic, trigonometric functions, roots, 
elevations, applied to the individual data of the sum

see an example of what I just said in the following formula: 
it calculates step by step the deviation standard of a part othe  


Openoffice damage in serious manner the file of excel with 
conditional calculations:

perform the following passages to verify the problem:

- create a file in excel (uses directly that enclosure) 
- open it in openoffice
- perform a small alteration and save in excel fromat 
- reopen the file in excel: establishes that the conditional
calculations with the specific syntax of MS Office give now a wrong 
- it is necessary to enter in the cell with F2, and to press ctrl + 
shift + return

the problem occurs every time that it is opened in openoffice

thank you for your kind attention
Comment 20 Unknown 2003-07-20 13:38:05 UTC
Created attachment 7875 [details]
example for the comment by mikedonovan
Comment 21 ooo 2003-07-22 14:44:24 UTC

Thank you for the comprehensive spreadsheet illustrating the behavior.

However, just a remark: Of course the calculation in cell E16
{=AVERAGE((((B5:B13=1)+(C5:C13=1))>0)*E5:E13)} does not give the
result you might expect if compared with the IF() syntax as it may be
used in MS Excel. But that also is no error, it just calculates what
you told it to calculate:

creates an array consisting of values 0 or 1 based on the comparison,
namely for rows 5 to 13: 1,1,1,1,0,0,1,0,1
This array is multiplied with the values of E5:E13
resulting in
of which the average is taken, which of course is 2888.89

The difference to the array condition used with the IF() syntax is the
handling of values where the condition is not fulfilled and no default
value for that branch is given, here Excel completely ignores the
value instead of using 0, and doesn't count it in the average.
Comment 22 jhuberman 2003-08-18 04:19:50 UTC
I hope that when the if() function is added it will include the 
functionality it has in Excel.  

I frequently use it with text as in IF(A1<A2,B1,No Good).  

Am I correct that this issue 4070 now includes the general 
implementation of the IF() function, and not just it's use within a 
SUM() function?
Comment 23 ooo 2003-08-29 16:17:02 UTC
Of course this is about general behavior of IF() and CHOOSE() in
array/matrix formulas, not just inside a SUM(). Changed summary to
reflect this better.
Comment 24 ooo 2003-11-06 21:15:43 UTC
Fixed on branch cws_srx645_cac.
Comment 25 ooo 2003-11-07 18:28:50 UTC
Reassign to QA.

Please find the specification at
and see the test case documents listed under the references section at
the end of that document. There you'll also find a slightly updated
version of the document that was attached to this issue.
Comment 26 frank 2003-11-13 11:58:17 UTC
Comment 27 frank 2003-11-13 11:58:47 UTC
reset fixed
Comment 28 frank 2003-11-13 11:59:40 UTC
now verified in internal cws cac on Solaris, Windows and Linux
Comment 29 frank 2004-03-16 14:05:26 UTC
found integrated on src680m30 using Linux, Solaris and Windows
Comment 30 daniel.rentz 2004-04-20 15:22:02 UTC
*** Issue 25898 has been marked as a duplicate of this issue. ***
Comment 31 frank 2004-07-09 09:10:48 UTC
*** Issue 31256 has been marked as a duplicate of this issue. ***
Comment 32 ooo 2006-12-13 20:14:14 UTC
*** Issue 6334 has been marked as a duplicate of this issue. ***