https://help.libreoffice.org/latest/en-US/text/scalc/guide/multioperation.html
2 Likes
Am I right in saying your spreadsheet has 2 input values and one output cell?
Perhaps you could build a lookup table of your input values and use vlookup (or hlookup) to select the values you want.
The two values could be output to the input cells of your spreadsheet?
Welcome @iamj5 !
I hope that the information in the link that @mikekaganski kindly provided was useful and that everything worked out for you.
As confusing as the calculations would be, if you have only one main result cell and no more than two input data cells (exactly as you described in your question), then the Data - Multiple Operations tool is exactly what you need.
Create a cross-table in the headings of which list all the necessary parameters and calculate all the necessary values in a few clicks.
2 Likes
Thanks. Multiple Operations looks like exactly what I need, and indeed, I tested it with a subset of my data and it worked great. I ran into trouble, though, when I used my full spreadsheet.
It runs for a while, calculating, and then where there should be output it just says “Err:504”.
The way my spreadsheet works, each row does a bunch of calculations and then the next row uses the result of that row to do its calculations.
If I make the Multiple Operations “Formulas” value reference a value perhaps 1000 rows down my spreadsheet, then it works just fine.
However, my full spreadsheet is something like 13,000 rows and so if I have Multiple Operations reference the result from the last row of the spreadsheet it gives this Err:504.
This makes it seem like there is some limit to the number of calculations that Multiple Operations can do, or a timeout of some sort?
If I just enter the input values manually in my spreadsheet (the same cells that I am telling Multiple Operations to change) then it works just fine. It just takes a while, about 30 seconds for LibreOffice to do all the calculations.
Does anyone know what might be going on? Or is there another way to do this that doesn’t involve Multiple Operations? Thanks.
Do you calculate the number of the last row in the table several times in the process of calculations in order to get a value from it? That being said, the table is large enough that the way to calculate that last number is slow? Or I misunderstood your vision of the problem?
Are you sure that the formulas do not contain implicit errors anywhere, which appear only with certain combinations of input parameters?
1 Like
The spreadsheet is time-series data, so each row depends on the result of the row before it. So if I change the input parameters it takes about 30 seconds before the final result is calculated.
I tested Multiple Operations with just 4 possible input parameters. I can use these same parameter values as inputs manually and the result is calculated fine. It is only when Multiple Operations is used that the error occurs.
@iamj5 This one needs investigation.
- Is this a regression, i.e. had this worked correctly in some older versions?
- Does this depend on any settings related to calculations? The first thing to check are use of OpenCL and multiple threads (
Options|LibreOffice|OpenCLandOptions|Calc|Calculate|Enable multi-threaded calculations, resp.)
I filed tdf#148982 for the spreadsheet I referenced in comment 8 above. But that results in Err:522, not Err:504, so possibly is a different thing. To be sure, you need to provide a sample showing the error for you.
Do you mean something like this - ErrDemo.ods (14.1 KB)
As far as I understand, Err:504 is obtained in the case of using range addresses when calculating a formula (at any stage of calculations), and Err:522 in the case of single cell addresses (just IMHO)
1 Like
Looks like you nailed it - hopefully @iamj5 confirms. I also consider this a bug, possibly a different one compared to my tdf#148982.
As far as I remember, this tool never worked reliably enough.
I did not report errors, because I believed that it was not the program that was mistaken, but myself …
Filed tdf#148983
Note that both bugs filed here are inherited from OOo, confirming what @JohnSUN said 
Err:504 in general means an argument type that the function does not expect for a parameter. Err:522 is a recursion into a circular reference.
In the attached document though the Err:504 particularly does mean a range reference was passed, because the formula cell to be replaced in MULTIPLE.OPERATIONS() is $A$13 that contains =SUM(A2:A12) but a range can not be replaced, e.g. in E4 the formula =MULTIPLE.OPERATIONS($A$13;$B$2;$D4;$A$2;E$3) should replace references to $A$2 with E$3 but this can’t be done here. It technically works without Err:504 if the formula in $A$13 was changed to
=A2+A3+A4+A5+A6+A7+A8+A9+A10+A11+A12
where then A2 could be replaced with E$3 but the results across the table would not be what (apparently) was expected because it is only replacing the A2 reference.
In short, MULTIPLE.OPERATIONS() was not implemented for this kind of usage.
But wouldn’t this all work if you not try to replace references to A2 with references to E3 in SUM, but instead replace A2 formula with =E3?
Erm? Sorry, what, I don’t quite follow…
So from your description, it looks like it tries to replace references to $A$2 with E$3. But that is indeed difficult. But why replace one with the other, when it should be possible to set $A$2's formula to be =E$3 temporarily? Then the value there will be as we need, and any existing references to $A$2 will keep working, obtaining the new value?
Yes that could work. However, it would be quite a rewrite of the current implementation where references are replaced when accessed. Technical discussion probably better in the bug…
tdf#130722 (see my comment 8 and comment 9 there)
Thanks all. I confirm that I see the Err:522 and Err:504 on your respective bug examples. My particular spreadsheet was hitting the Err:504, but when I tried to create a simple reproduction I encountered the Err:522 as well.
In the meantime until these bugs are fixed, I’m wondering if a macro function could be a workaround? It seems like a macro should be possible to do a similar operation correctly, essentially filling in 2 input cells with some values and then copying the output cell to another cell, then repeating a certain number of times.