Issue 111808 - Multiple Operations calculations
Summary: Multiple Operations calculations
Alias: None
Product: Calc
Classification: Application
Component: code (show other issues)
Version: OOO320m9
Hardware: All All
: P3 Minor (vote)
Target Milestone: ---
Assignee: AOO issues mailing list
QA Contact: Rob Weir
Depends on:
Reported: 2010-05-25 02:12 UTC by tyleong
Modified: 2017-05-20 10:44 UTC (History)
4 users (show)

See Also:
Issue Type: DEFECT
Latest Confirmation in: 3.4.1
Developer Difficulty: Simple


Note You need to log in before you can comment on or make changes to this issue.
Description tyleong 2010-05-25 02:12:11 UTC
Suppose you want to collect the values of the sum of 3 throws of a dice (or an 
even more complicated problem in financial analysis). I would set up three 
cells with Randbetween(1,6) and a fourth cell to sum these three cells.
Next, I would set up a Multiple Operations (i.e., DataTable in Excel) to 
collect thousands of replications of that sum. This is done by setting any two 
blank cells as the row and column input cells. I know we can use relative 
referencing to set up the table but this approach cannot apply in the actual 
problem in financial analysis. 
Using Excel’s DataTable to collect replication results for such Monte-Carlo 
simulations works fine, but not Calc’s Multiple Operations. Specifically, 
Multiple Operations will give the same answer for all the replications. Thus, 
the implication that recalculation is not triggered in the algorithmic steps in 
Multiple Operations.

I can submit example (xls and ods) files but don't know how.
Comment 1 Regina Henschel 2010-05-25 07:38:30 UTC
RANDBETWEEN is implemented in a way, that it does not recalculate automatically.
So you have to use a "Recalculate Hard" (Ctrl+Shift+F9) to get new random values.

Concerning "multiple operations" I need an example document from you. You can
attach it, if you use the link "Create a new attachment" in the issue tracker form. does not implement an own random value generator but uses that
one, which comes with the compiler. It has only 2^16 different values. Are you
sure it is appropriate for a Monte-Carlo simulation? 
Comment 2 Rob Weir 2013-02-24 21:37:46 UTC
@Regina, I agree with your observation that RANDBETWEEN() does not calculate automatically.  But is that a reasonable behavior?  RAND() certainly recalculates with every F9.  Why should RANDBETWEEN() do differently?  Is is just a scaled/translated version of RAND(), right?

I'm wonder if this should be confirmed as a DEFECT.
Comment 3 Rob Weir 2013-04-03 13:38:38 UTC
Confirming as a DEFECT.  I don't see any argument for why RAND() is treated as a volatile function but RANDBETWEEN()is not.
Comment 4 samalipraveena 2014-10-02 01:11:24 UTC
regina, rob,
I took this task and was looking at the code to find out why RANDBETWEEN is not updated automatically; and another thing is that I couldn’t understand what the user is trying to do. With AOO calc multiple operations, you can input only two sets of data a row and a column for a formula. Can you explain more about this or generate test data, please?
In my system, when I press F9 key, it increases the sound, but doesn’t update RAND. However, Ctrl+shift+F9 updates both RAND and RANDBETWEEN() cells.
These are the things I got from code:
RAND is an OO or built-in function with an opcode defined in compiler.hrc file, which has opcodes for all spreadsheet functions.
On the other hand, RANDBETWEEN() is an add-in function and they are not assigned any specific opcodes. As far as I analysed the code, Add-in functions have an opcode of "opexternal".
In main/sc/source/core/tool/odffmap.cxx, there is a collection which holds the list of add-in functions in analysis, including RANDBETWEEN; when i searched the code to find out where this collection has been used but not anywhere. 
The implementation of this function is in main/scaddins/source/analysis - analysis.hxx, analysis.cxx files.
In /aoo-trunk/main/formula/source/core/api/FormulaCompiler.cxx, certain functions, including ocRandom-RAND, are set to have to be always recalculated, using the code pArr->SetRecalcModeAlways(); Like ocRandom-RAND, RANDBETWEEN or other add-in functions don’t have any opcode and so they can’t be set to SetRecalcModeAlways and updated automatically; 
This RecalcModeAlways is not set for Add-in functions, instead like all formula cells it is set to recalconload or recalconloadonce.
I have already spent days in this task analyzing the code; can you please tell me your suggestions on this issue?
Comment 5 Regina Henschel 2014-10-02 22:46:27 UTC
See my comment #1, I too have no idea, what tyleong wants to do with Multiple.Operations.

I think, that RANDBETWEEN should stay as it is. It is typically used in education where you want the content of the spreadsheet to be stable until the work of the pupil is done.

Users who want to have new random numbers with every cell change, can write their own function using RAND, e.g.

In regard to RANDBETWEEN this is duplicate to issue 69903. Find a related patch there.

In regard to Multiple Operations "needmoreinfo" keyword would be appropriate.
Comment 6 samalipraveena 2014-10-03 04:37:09 UTC
Hi regina,
Thanks for your comment. I saw the patch with the issue 69903. It is a patch which affects so many files with so many lines of code. 
As you said, i'm going to leave this issue as it is.
Comment 7 Andreas Säger 2014-10-04 14:41:30 UTC
RANDBETWEEN behaves exactly like any other non-volatile spreadsheet function.
RANDBETWEEN(A1;B1) automatically recalculates when either one of A1 or B1 changed.
Likewise, SUM(1;2;3) never recalculates because constants are constants.
Comment 8 Marcus 2017-05-20 10:44:39 UTC
Reset the assignee to the default "".