Calc: Getting list of output values for a set of input values

English
#1

Hello,

I have a complicated spreadsheet with 2 input cells. Changing the values in these input cells affect a lot of formulas, but eventually there is a value in an output cell at the bottom, and that’s what I’m interested in.

I’d like to be able to have LibreOffice take a set of input values for the input cells (like 0 to 10 for each) and automatically try every combination, and give me the result from the output cell for each combination.

This can be done by hand but it is tedious for a lot of possible inputs so I’m thinking there must be a way to have LibreOffice do it automatically.

It’s kind of like the Solver, but I’d like the actual results for each inputs tested rather than just optimizing to one final value.

Does anyone know how this can be done? Thank you!

Why Multiple Operations only supports simple arithmetic operations but not some complex built-in functions in LibreOffice Calc
#2

https://help.libreoffice.org/latest/en-US/text/scalc/guide/multioperation.html

2 Likes
#3

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?

#4

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.

multioperation

2 Likes
#5

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.

#6

image

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
#7

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.

#8

Looks like something is really suspicious with long chains…

@erAck : should I report this?

#9

@iamj5 This one needs investigation.

  1. Is this a regression, i.e. had this worked correctly in some older versions?
  2. Does this depend on any settings related to calculations? The first thing to check are use of OpenCL and multiple threads (Options|LibreOffice|OpenCL and Options|Calc|Calculate|Enable multi-threaded calculations, resp.)
#10

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.

#11

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
#12

Looks like you nailed it - hopefully @iamj5 confirms. I also consider this a bug, possibly a different one compared to my tdf#148982.

#13

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 …

#14

Filed tdf#148983

Note that both bugs filed here are inherited from OOo, confirming what @JohnSUN said :slight_smile:

#15

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.

#16

@erAck:

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?

#17

Erm? Sorry, what, I don’t quite follow…

#18

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?

#19

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…

#20

tdf#130722 (see my comment 8 and comment 9 there)

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.