cannot iterate random numbers 1000 times

I want to run 1000 iterations on some numbers automatically. I need the random numbers to update on each iteration, But F9 CANNOT do this. Do I need a monte carlo simulation add-in ?

F9 can run 1000 iterations automatically but it WONT update the random numbers so its really only like 1 iteration. Only when E1>=6 does it iterate 1000 times.

example.

cell A1:A10 =RAND()
cell B1:B10 =SUM(RANDBETWEEN(0,1))

cell E1 =SUM(B1:B10)
cell E2 =IF(E1>=6,1,0)
cell E3 =IF(E2=0,E3,E3+E2)

set tools > iterations to "1000" and calc. to "automatic" and you will see

Think of it as flipping 10 coins at once. If it lands heads 6 or more times then I count that as "1" win. I want to repeat it 1000 times.
F9 wont do it. It waits for when I flip 6 or more heads and then cycles 1000 on the counter without actually running a real simulation.

Because I'm more interested in knowing why I cant get excel to run 1000 iterations automatically to measure it imperically - rather than just computing the actual formula. My example was just an example. The real question is my original question.

Sorry for any confusion. I'll try to be clear what I mean.

Forget the 1000 iterations for a moment. Every time I hit F9 it will make a single calcluation (if set to 1 iteration). If I hit F9 1000 time manually, I expect the result to be near 37. And it is! ( i.e. 37 times out of 1000, there will be >6 1's). I can do this by hitting F9 1000 times. But I want excel to automatically run 1000 iterations such that I will see a result of approximately 37 -- as if I manually hit the F9 button 1000 times.

Here are ALL the cells:
B1-B10: =SUM(RANDBETWEEN(0,1))
E1: =SUM(B1:B10)
E2: =IF(E1>=6,1,0)
E3: =IF(E2=0,E3,E3+E2)
E4: =E3/1000

Notice what happens if iterations is set to "1" and you hit F9 1000 times. This is NOT the same as setting the iterations to 1000.

So my question is how to get the result of about 37 after 1000 iterations automatically without having to hit F9 1000 times ?

correction to my last post. It should be about 370 out of 1000 (37% +/- a few), not 37

If you just want to recalculate your sheet a 1000 times this should work...

[#]
Private Sub someWierdStuff()

For i = 0 To 1000
Application.Calculate
Next i

End Sub

[#]

Alternatively this does the sum as well and returns answer to F1

Please Login or Register  to view this content.

Is this what you wanted?

Thanks for the help people.