Issue 4070

I'll have a look, Peter

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

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:

    =sum(if((C3:C20='T')*(B3:B20='R');D3:D20))

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.

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
work.

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

{=(C3:C20="T")*(B3:B20="R")}

and the final formula reading

=SUMIF(E3:E20;1;D3:D20)

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 
work:

<quote>

Now enter the following formula as a matrix formula:
=SUM((A1:A40>=C1)*(A1:A40<C2)*B1:B40)
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.
{=SUM((A1:A40>=C1)*(A1:A40<C2)*B1:B40)}

<quote/>

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 
work.

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.

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 
bug.

*** Issue 4265 has been marked as a duplicate of this issue. ***

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.

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
like.

Best regards, Peter

*** Issue 5857 has been marked as a duplicate of this issue. ***

Hi.
Any idea about the planned timing of this enhancement?
I'm just waiting for this for dropping MS-Office in the recycle 
bin...;-)

Best regards, Rob

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
Rob

Just for the record:




Sum(If(X);Y;Z)




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




Sum(Z+(Z-Y)*(X))




X, Y or Z can be matrix references.




Perhaps this helps someone to get rid of MSO?

*** Issue 11731 has been marked as a duplicate of this issue. ***

This is mine..

Started.

*** Issue 16989 has been marked as a duplicate of this issue. ***

 
NOTE: ALL THE INFORMATION IN THIS COMMENT ARE RELATIVE TO OPENOFFICE 
1.1 RC
 
first of all please open the file I have attached
 


:::::: IMPOSSIBILITY TO USE CONDITIONS IN FUNCTIONS THAT ARE NOT "SUM"
   
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 
Ctrl+shift+return).  
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 


:::::: ERRORS WITHOUT WARNING 

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 
spreadsheet.

:::::: LIMITATIONS OF CONDITIONAL FORMULAS IN OPENOFFICE 

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  
elements

=SQRT((SUM((((B5:B13=1)+(C5:C13=1))>0)*1)*SUM((((B5:B13=1)+(C5:C13=1))
>0)*E5:E13^2)-(SUM((((B5:B13=1)+(C5:C13=1))>0)*E5:E13))^2)/(SUM
((((B5:B13=1)+(C5:C13=1))>0)*1))^2)

:::::: ALTERATION OF THE FILE OF EXCEL 
¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯ 
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 
result 
- 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

Created attachment 7875 [details]
example for the comment by mikedonovan

Mikedonovan,

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:

(((B5:B13=1)+(C5:C13=1))>0)
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
2000,3000,9000,10000,1000,2000,1000,1000,1000
resulting in
2000,3000,9000,10000,0,0,1000,0,1000
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.

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?

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.

Fixed on branch cws_srx645_cac.

Reassign to QA.

Please find the specification at
http://specs.openoffice.org/calc/compatibility/cac/conditional_array_calculation.sxw
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.

reassigned

reset fixed

now verified in internal cws cac on Solaris, Windows and Linux

found integrated on src680m30 using Linux, Solaris and Windows

*** Issue 25898 has been marked as a duplicate of this issue. ***

*** Issue 31256 has been marked as a duplicate of this issue. ***

*** Issue 6334 has been marked as a duplicate of this issue. ***