Issue 78165

Summary: Rand() Function and Data Tables (Multiple Operations)
Product: Calc Reporter: jpdaley <jpdaley>
Component: editingAssignee: AOO issues mailing list <issues>
Status: CONFIRMED --- QA Contact:
Severity: Trivial    
Priority: P3 CC: ace_dent, issues
Version: OOo 2.2   
Target Milestone: ---   
Hardware: All   
OS: All   
Issue Type: ENHANCEMENT Latest Confirmation in: ---
Developer Difficulty: ---
Description Flags
Example of Problem
Financial Spreadsheet from Excel Displaying Expected Behavior. Data Table is in B32 through D281 none

Description jpdaley 2007-06-07 05:49:01 UTC
I am using version 2.2 on Mac OS X v10.4.9
In the Calc program 

There appears to be a problem with how the Rand() function works in
conjunction with data tables (multiple.operations).
If the formula used for the data table involves a random number
directly (such as NORMINV(RAND(),10,2)) then the data table will be 
populated with random numbers. If however, the formula used for the
data table is a function of other cells involving random numbers
(such as "=(C4+C5)/2" where C4 and C5 are each randomly generated
numbers using NORMINV(RAND(),10,2) then the data table will NOT be
populated with randomized numbers, but rather only 1 number.

The Random function is not acting as a truly volatile function as it should.  OpenOffice is only forcing it 
to be volatile if it is the directly in the cell used for the Formulas Input.  It is not volatile if it is in 
another cell then used as part of the formula used in the Formulas Input cell.

Here are the details of how to reproduce the problem.
Set cell A1 = RAND()
Set cell A2 =RAND()
Set cell A3 = A1+A2

Setup a datatable by entering numbers 1 through 5 in cells A6 through A10, highlighting cells A6 
through B10 and clicking Data - Multiple Operations.
For the Formulas input, select either A2, or A3 (see further discussion below)
For the Column input, select any empty cell (say C1).

IF I use A2 in my Formulas input above, I get random numbers in my data table (as expected).
IF I use A3 in my Formulas input above, I get a single number down the table (not random). This 
number will be equal to the current value of A3, even though it should be randomized as in the A2 

This functionality is very important in simulation spreadsheet models where the output is a function of 
multiple inputs that may have random distributions.  This is used for Engineering and Finance models 
and would be very valuable to fix in OpenOffice.

I have also confirmed that this issue exists in the Windows version of OpenOffice.

Comment 1 jpdaley 2007-06-07 05:50:56 UTC
Created attachment 45704 [details]
Example of Problem
Comment 2 ace_dent 2007-06-07 14:15:23 UTC
Is this a duplicate of Issue 67135 ?
IMO, I can see that this current behaviour may be through design. If I were to
hold a random number in A1, then want to use that number in two different
equations (B1 & B2), I would want to use the same rand number not a volatile
value. If I wanted different rand numbers for B1 & B2, I would include a
'rand()' in each of those equations... but I might be missing the point...
Comment 3 jpdaley 2007-06-07 15:13:00 UTC
This may very well be the same root problem as described in Issue 67135 but I am not possitive.

They both appear to be related to the volatility of the Rand() function.  It is not behaving as truely 

As far as design, I can only say that Excel works the way I describe as "expected" and this feature is 
used extensively in financial and engineering modeling involving uncertain inputs.  If I have several 
variables all that have some random distribution, and I have an output that is a function of those 
inputs, I want to be able to simulate what the distribution of those outputs would be.  You do this by 
creating a data table of the output.  You can then measure the statistics on the output data table.  If the 
Rand() function is not working as expected however, and you only get one value in the data table, you 
cannot determine what distribution the output will have.

I will attach a financial spreadsheet created in Excel that does what I am describing.  Although I know 
we may not be trying to copy excel directly in OpenOffice, I think that the way Excel functions on this 
point is more valuable.
Comment 4 jpdaley 2007-06-07 15:17:53 UTC
Created attachment 45731 [details]
Financial Spreadsheet from Excel Displaying Expected Behavior.  Data Table is in B32 through D281
Comment 5 frank 2007-08-01 14:43:25 UTC
Hi Eike,

is it a double to Issue 67135 ?

If so, please close as such.

Comment 6 ooo 2007-08-01 16:49:54 UTC
Not a duplicate of issue 67135.

I see the difference between how Calc and Excel behave in the
multiple.operations case, I wouldn't call the Excel behavior being expected
though. It is rather obscure and I second the opinion of ace_dent in #desc3 and
if not in {TABLE()} Excel does it differently, two formulas using the same cell
containing RAND() do produce identical values.

Anyway, if people rely on that we may as well adapt Calc's behavior.
Comment 7 ooo 2008-05-30 16:03:35 UTC
Propably not doable in time frame for 3.0, retargeting to 3.x
Comment 8 Marcus 2017-05-20 11:11:48 UTC
Reset assigne to the default "".