# 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: ---
Attachments:
Description Flags
Example of Problem
none
Financial Spreadsheet from Excel Displaying Expected Behavior. Data Table is in B32 through D281 none

 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 case. 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. Thanks, Jon``` jpdaley 2007-06-07 05:50:56 UTC ```Created attachment 45704 [details] Example of Problem``` 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...``` 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 volatile. 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. ``` 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``` frank 2007-08-01 14:43:25 UTC ```Hi Eike, is it a double to Issue 67135 ? If so, please close as such. Frank``` 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. ``` ooo 2008-05-30 16:03:35 UTC `Propably not doable in time frame for 3.0, retargeting to 3.x` Marcus 2017-05-20 11:11:48 UTC `Reset assigne to the default "issues@openoffice.apache.org".`