Apache OpenOffice (AOO) Bugzilla – Issue 78165
Rand() Function and Data Tables (Multiple Operations)
Last modified: 2017-05-20 11:11:48 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
Created attachment 45704 [details] Example of Problem
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...
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.
Created attachment 45731 [details] Financial Spreadsheet from Excel Displaying Expected Behavior. Data Table is in B32 through D281
Hi Eike, is it a double to Issue 67135 ? If so, please close as such. Frank
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.
Propably not doable in time frame for 3.0, retargeting to 3.x
Reset assigne to the default "issues@openoffice.apache.org".