When in libreoffice I set up a whatif analysis with a DSUM formula that gets the value of the criteria from the row input and the column input of the whatif table, I get an error 504. When I enter the value of the criteria manually the DSUM formula calculates correctly. What is causing the problem when the value of the criteria is coming from the row and column input of the whatif table and how do I solve this problem?
I found a solution for the problem. I let the row and column input refer to a helper cell and in the criteria I use the TRIM function on these helper cells. This solved my problem. The helper cells should also not be left empty, otherwise all cells in the WhatIf table are giving the value for empty criteria.
I leave the question and the solution that I found. I could become useful for someone else struggling with the same problem.
If someone would have more elegant solutions, they would be very welcome.
DemoFile.ods (454.8 KB)
Difficult, without the actual file, but as you use TRIM your cell may have contained text, where DSUM needed a number. Then a simple conversion via VALUE() may have solved your problem.
https://help.libreoffice.org/6.3/en-US/text/scalc/05/02140000.html
Hereby also a demofile to illustrate the problem.
DSUM accepts text input for the criteria.
Apparently the problem is that the row and column input from the WhatIf Analysis (‘Multiple Operations’) is giving a type that is not accepted as value for the criteria. Using a helper cell for the row and column input and converting it to TEXT or doing a string operation on it like TRIM solved the problem.
Strange and not perfect, but it works.
DemoFile.ods (454.8 KB)
Hello Paul,
I believe that your problem has to do with the combination with multiple operations.
You can also solve your analysis without database functions. For example, SUMIFS() or a PIVOT table will work. See my example.
DemoFile_new.ods (515,8 KB)
For the writers who are already starting to cringe. Of course, it would make more sense and would be best if you used a BASE database for such tasks. But I have not provided this for you.
Thanks for the suggestion. The SUMIFS function is indeed a nice alternative.
I’m making some quick analyses on different datasets that’s why I’m not using a database. It’s easier to just open the csv files in a spreadsheet than to import them in database tables.