Apache OpenOffice (AOO) Bugzilla – Issue 4070
IF() and CHOOSE() in array/matrix formulas (was: matrix formula in sum doesn't work)
Last modified: 2013-08-07 15:15:02 UTC
A matrix formula of the form: =sum(if((C3:C20='T');D3:D20)) 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."
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. ***