Making rand() update every iteration

[incblue]

I do not have a spreadsheet as an example.
I will create a new one to illustrate my problem if my explanation is confusing.

When using maximum iteration of 1, the rand() function clearly generates a new value every time you press F9.

However sometimes when I use a maximum iteration of 100, 1,000 or 10,000 it is clear that Excel has just generated one random value, and used this value for all 10,000 calculations instead of generating a new value every time.

Is there a way to force Excel to generate a new value every iteration?

 

[mirabeau]

Have you tried looking at the Excel options.

Specifically Calculation option (under formulas in Excel 2007), and is this set on manual? If so, change it to automatic.

 

[incblue]

Have you tried looking at the Excel options.

Specifically Calculation option (under formulas in Excel 2007), and is this set on manual? If so, change it to automatic.

This does not help.
On both manual and automatic only 1 number is generated for 100 iterations.

 

[incblue]

Bump

 

[mirabeau]

So what, specifically, are these iterations you refer to?

Can you give examples so maybe others can replicate the problem.

 

[incblue]

Okay, I have thought of a way of illustrating my problem:

In cell A1 put =rand()

In cell B1 put =B1+A1, this will keep a running total of the random numbers.

In cell C1 put =C1+1, this is an iteration counter.

In cell D1 put =B1/C1, this calculates the mean.

Use any method you want to reset these values to 0 before you start.

When you calculate one iteration at a time, the current random number (in cell A1) is clearly different to the mean.

However if you iterate 10,000 times, the mean will be equal to A1.
In reality the mean should be very close to 0.5
This shows that Excel is not generating a new random number every time.

In addition, after 10,000 iterations the running total (in cell B2) is equal to 10000*A1. This shows that the first value generated in cell A1 is being used 10000 times over.

 

[mirabeau]

Okay, I have thought of a way of illustrating my problem:

In cell A1 put =rand()

In cell B1 put =B1+A1, this will keep a running total of the random numbers.

OK. I did these and got a circular reference complaint from Excel about the B1 entry.

Is this really what you mean?

If you're circular referencing, it's not surprising to me you don't get the results you apparently want.

What is it that you're really trying to do?

 

[incblue]

OK. I did these and got a circular reference complaint from Excel about the B1 entry.

Is this really what you mean?

If you're circular referencing, it's not surprising to me you don't get the results you apparently want.

What is it that you're really trying to do?

Sorry, I ommited a step in my instructions.

I have been doing this with iterative calculations turned on, and maximum iterations set to 10000 (or set to 1 for comparison)

I know for sure that the set of cells I have described exhibit the behaviour I have described.
Could you please try again with iterative calculations turned on?

 

[shg]

Set iterations to 100, and maximum change to 0 (to minimize the iteration stopping due to a small number, though you could still get a 0).

In A1, enter 1

In A2, =IF(A1, 0, A2+RAND())

Then set A1=0; you'll see a number close to 50. Press F9 several tiems, and observe and it will increase by about 50. It's generating a new number in each iteration.

 

[incblue]

Set iterations to 100, and maximum change to 0 (to minimize the iteration stopping due to a small number, though you could still get a 0).

In A1, enter 1

In A2, =IF(A1, 0, A2+RAND())

Then set A1=0; you'll see a number close to 50. Press F9 several tiems, and observe and it will increase by about 50. It's generating a new number in each iteration.

I have tried your suggestion, and you are correct, even for 10000 iterations. There is clearly no problem with RAND() in that case.

However I have finally found a way to show my problem:
If you set up A1 to D1 like this, with these calculation settings, then I am sure you will see what I mean.