Description: I have a valid call to DSUM. It works when I have a simple data table and a simple criteria table, searching for one value in one field. Then I tried to populate the value I'm searching for using Multiple Operations, and I get a 504 error. the variable is a text cell in the first row of the criteria table. If I use a simple formula (LEFT() ) then the table is populated correctly. But if the formula is a call to DSUM, with the variable being the text value in the first row of the criteria table, I get 504 errors. I haven't seen any reason this wouldn't work. I no longer have access to Excel but this is something I often did in Excel years ago. Steps to Reproduce: 1.Create simple data table 2.create simple criteria table 3.write formula using DSUM with the criteria being a match for a simple string in one field. Notice that the formula evaluates correctly 4. create a multiple operations table for one variable, three formulas. The first formula is LEFT(X,2) where X is the string in the first row of the criteria. The second formula is DSUM(DATATABLE, 3,CRITERIA). I expect to see the sum of column 3 for matching rows. The third formula uses column 2. The fourth formula uses RIGHT(X,3) Actual Results: The LEFT and RIGHT formulas evaluate correctly but the DSUM formulas do not. I get 504 errors for all the DSUM calls Expected Results: I expect to see the DSUM call computed correctly for each value in my table plugged into the second row of the criteria field. When the populated value is the sample constant plugged into the first row of the criteria table, I expect to see the DSUM evaluation identical to when the formula is calculated normally, not in a table. Reproducible: Always User Profile Reset: Yes OpenGL enabled: Yes Additional Info: I'll post a simple spreadsheet.
Created attachment 157933 [details] The simple spreadsheet illustrates the problem. I checked ask.libreoffice.org first and found one entry that seemed nominally to describe the sort of problem I found, and one of the comments suggested a posting of a bug report. That's why I came here.
This seems like a DUP of bug 105683 to me. See for example https://bugs.documentfoundation.org/show_bug.cgi?id=105683#c2 *** This bug has been marked as a duplicate of bug 105683 ***
(In reply to Alex Thurgood from comment #2) > This seems like a DUP of bug 105683 to me. bug 105683 is about inline arrays in general, while this one is about something different, and no inline arrays are involved here in any way. Additionally, OP has mentioned in [1]: > What I did was to buy an MS Office subscription So it *looks like* an interoperability issue. It would be nice to check what Excel does in this case. [1] https://ask.libreoffice.org/t/why-multiple-operations-only-supports-simple-arithmetic-operations-but-not-some-complex-built-in-functions-in-libreoffice-calc/48096/6
(In reply to Mike Kaganski from comment #3) > So it *looks like* an interoperability issue. It would be nice to check what > Excel does in this case. Note that trying to re-create the calculation using Excel 2016's What-If analysis (one-variable data table [1]) on the data from attachment 157933 [details], moving F4:F5 to F10:F11, I4 to H10, clearing H11:H13, selecting G10:H13, running the Excel's "Data Table", and selecting G11 as "Column input cell", I get "Input cell reference not valid" error from Excel. Indeed, I might do it all wrong; a sample spreadsheet doing this from Excel would hopefully clarify. [1] https://support.microsoft.com/en-us/office/calculate-multiple-results-by-using-a-data-table-e95e2487-6ca6-4413-ad12-77542a5ea50b
Created attachment 179999 [details] This excel file shows how the computations work in Excel I don't remember much about this issue but it has resurfaced so I'm doing my best to show what works in Excel but not LibreOffice. I don't remember whether I had to tweak the formulas to reflect application differences, but the computations were straightforward analogues. I'm unable to demonstrate the problem by opening the ods file in Excel and showing the success, but when I open this XLS file in ODS it demonstrates the problem in LibreOffice.
My original report two years ago showed an ODS file that demonstrated the failure of a calculation. I just added an Excel file that computes correctly in Excel, but when I open it in LibreOffice, there's an error.
Thanks Dan; your sample is really helpful!
(In reply to Dan Chall from comment #5) A *workaround* would be to make the input cell be outside of the range mentioned in the formula cell. Namely, DSUM formula (used in the MULTIPLE.OPERATION and Excel's table) refer to F4:F5 range; and the input cell (the variable which value gets changed in the MULTIPLE.OPERATIONS/table) is F5 which is part of the mentioned range. If instead you put "Bill" to, say, G5; and change B5 to '=G5'; and change input cell of the MULTIPLE.OPERATIONS/table to G5 (so it's not part of resulting formula's ranges), then it would work.
Eike: it *seems* to me that the replacement of the input cell's value should be implemented as if the input cell had formula '=<reference_to_replacement_cell>', right? Not by modifying something else?
(In reply to Mike Kaganski from comment #8) Nice workaround. Confirming that I updated the excel spreadsheet with the recommended fix, and that the formulas compute correctly when the sheet is opened in LibreOffice. Thanks.