Recording values of multiple iterations

[The Wiseman]

Hey everyone,

So I have a table which is being generated by a certain function along with a randomly generated number. The value I'm interested in is the sum of the whole table. Now what I want to do is generate a random number X (in this case 100) times and record the sum of the table every iteration. So far I've only managed to "copy" --> "paste value" 100 times and this gives me the required result however this takes a lot of time and clicking and frankly it's making me feel quite ashamed.

Is there an efficient way that excel can do this for me? A couple of internet searches have pointed towards a macro however I've been playing around with this and my limited Matlab knowledge is not getting me very far.

I've been tempted to just run my data through another program, but this is an Excel based project.

Thanks!

[JBeaucaire]

With a little more detail, we can make a simple macro simulator that runs your sheet for you, however many loops you wish.


1) What are the criteria for the "random" number? The range? Are duplicate numbers allowed in the random generation, meaning we don't care what the random numbers are within the criteria?

2) What sheet name is all this running on?

3) What cell should the random number be entered into?

4) What cell(s) should then be summed on each iteration?

5) Where should the final results be placed? Do you want all the individual results recorded as well, or just the final result each time?

[The Wiseman]

Thanks for the quick reply!

1) What are the criteria for the "random" number? The range? Are duplicate numbers allowed in the random generation, meaning we don't care what the random numbers are within the criteria?

Yes duplicate numbers are allowed, I simply used the rand() function to generate these.

We're performing volume estimates on a reservoir so there are about 4 steps (sheets) before this "final step". If possible the function should perform a similar action as the "calculate now" button and then record the sum of the table.


2) What sheet name is all this running on?

The sheet which contains the necessary information is called GRV.


3) What cell should the random number be entered into?

Right now I'm generating the random number in cell H2.


4) What cell(s) should then be summed on each iteration?

Cells B20:O34

5) Where should the final results be placed? Do you want all the individual results recorded as well, or just the final result each time?

Preferably just in a column on the same sheet say S1:S100. All I'm interested in is the final sum during each iteration, so 100 values.


Hope this is enough information

[JBeaucaire]

TO be clear, you're wanting to just "Calculate Now" to cause the existing RAND() function to change, then capture the sum of all the cells into S1, then Calculate Now and capture the sum in S2, etc?

[JBeaucaire]

THus, I would think something like this:

Please Login or Register  to view this content.

[The Wiseman]

Yes, that's it! Pretty simple right? Maybe there's a way to do this without a macro? Everytime we change a parameter in the previous steps we need another 100 iterations which is why I'm desperately looking for a way to automate this.

[JBeaucaire]

To force a sheet to calculate 100 times and record the answers somewhere static, that requires a macro.

[The Wiseman]

Wow that runs perfectly! We need to run 10000 iterations of the final volumetrics estimate so this is great. Thank you very much.