I’m not quite sure how to ask this question, so please forgive me:
I have a calc worksheet in LibreOffice 6.4.7.2. The worksheet has an input at cell B2, and produces a result at cell K2786. I want to calculate the result for several thousand different values of cell J2 and record them all in a table.
How can I do this?
Thank you.
Surely MultipleOperations is your friend.
https://help.libreoffice.org/latest/ro/text/scalc/guide/multioperation.html
Thank you, Lupp!
I didn’t know about MultipleOperations. It would indeed be my friend, if it weren’t for this bug: “Multiple Operations fail to work with long sequences of dependent cells.”
Any other suggestions?
Scenarios.
See my today’s comments on the linked bug.
Did you check for the absence of “the bug” in scenarios? After all the implementations of scenarios and MULTIPLEOPERATIONS() may be based partly on the same core-code.
Thanks, Lupp. Honestly I can’t figure out how to use Scenarios; the help page doesn’t offer a very detailed explanation; and when I try to follow the instructions, it doesn’t behave the way I think it should. Could just be me not understanding how it works. But in any case, unless I’m completely misunderstanding how it is supposed to work, Scenarios seems poorly suited to testing hundreds or thousands of different scenarios and recording the results in a table (vs. testing half a dozen different scenarios one by one).
Sorry. I rarely used scenarios myself, and can’t even remeber if there ever was success at all. There may have been a reason for what I stopped to try many years ago. Anyway I assume -like you- that the creation of a table of results depending on one or two input parameters taken from prepared sequences isn’t their purpose. That’s what MULTIPLE.OPERATIONS is made for.
Probably you will need to collect the “complicated” calculations you need to get your results in a UserDefinedFunction. Not knowing any details I can’t even guess.
Thanks. It’s a time series analysis over thousands of periods, wrapping in actual historical data for each of the thousands of periods… I can’t even imagine how to write it as a user defined function, though probably someone else could. In lieu of Multiple.Operations, I suspect that the right answer–for me, since I know R–is to rewrite it in R instead. Just not nice and visual, like Calc. Your help much appreciated, though.
No, they are completely different things. Scenarios is an area of a sheet with several data sets, of which one is active and filled into the sheet’s scenario area. And scenarios for “several thousand different values” isn’t quite what they are made for and would be cumbersome to use.
Calculating the same sheet for a huge amount of input values sounds more like a script/macro would be the way to go.
@JohnSUN already showed much of that way in a working example.
(And probably the mentioned bug will be fixed soon.)
A scenario is just a cell range on a hidden sheet. When you select a scenario, its cells are “imported” into a scenario range.