How do I perform a monte carlo simulation in Open Office?

Question

Hello all.

I am trying to generate some ranges for a problem I am working on. These rangers are going to be based on the sum of the ratio's of a bunch of numbers. So for example, the constant's are 5 6 and 7.

The ranges I get will be 5/x + 6/y + 7/z = S

I want x, y, and z to come out of a list of numbers I have - say .5, .6, .7, .8, .9, and 1

So If I run 100 iterations of this, I want the spreadsheet to randomly fill a value in X from that list of numbers, another random selection for y, and yet another for z.

And like I said, I want that sum, S, to be calculated 100 times in such a way that I will get a range of values for S.

I have been trying to figure out how to do this without the use of macros.

Answer 1

Here's one way to do it. Create a table of x, y, and z input values. Put a column to the left of the table with the number of each input value (1...N). Say that you have 10 potential input values for each. So your table is in A1:D10 with 1 through 10 in column A and the x values in B, y values in C, and z valued in D.

Then you can select a random value of the x values by writing =VLOOKUP(10*RAND()+1,$A$1:$D$10,2,TRUE). This randomly selects a number between 0 and 10 and looks up the x value matching the A column that matches the number, rounded down. E.g. the random number is 4.3 -- then it will select the 4th value. Replace the third parameter in the VLOOKUP column with 3 for y values and 4 for z values...

If you don't have any other data in columns A:D, you can generalize this with =VLOOKUP(count($A:$A)*RAND()+1,$A:$D,2,TRUE).