From: Paul on
On Fri, May 21, 2010 at 2:09 PM, LEONG Thin Yin <tyleong(a)smu.edu.sg> wrote:

> Hi,
>
>
>
> I have been using DataTable in Excel to collect replication results of
> Monte-Carlo simulations. A Monte-Carlo simulation is a spreadsheet model
> that contains some cells with Rand and/or Randbetween functions. Even
> without any change in input values, if there are any, clicking F9 would
> change the output results. By setting up a DataTable with blank cells as
> row and column input cells, the results of the simulations can be
> collected automatically (without need for macros).
>
>
>
> When I try to do the same in Calc using Multiple Operations, all the
> collected results are the same. This suggests that Calc does not
> recalculate with each change in values in row and column input cells in
> Multiple Operations. Or may be, it is too intelligent to detect that
> there is really no change in those cell values.
>
>
>
> I am using a macro instead in Calc (not needed in Excel) to overcome
> this problem. Any idea if there are any "option setting" in Calc that
> would allow me to do it the DataTable way. May be, the next version of
> OpenOffice.org Calc can be improved for this. Not sure how to feedback
> this to the development team.
>
>
>
The rand/randbetween functions normally recalculates when pushing F9 in
calc. I've found the spreadsheet in the following tutorial (for monte carlo
sim's) works just fine in calc as it does in excel.

http://www.vertex42.com/ExcelArticles/mc/SalesForecast.html

/paul





>
> thinyin
>
>