Making rand() update every iteration

incblue

New Member
Joined
Feb 23, 2011
Messages
8
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?
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
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.
 
Upvote 0
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.
 
Upvote 0
So what, specifically, are these iterations you refer to?

Can you give examples so maybe others can replicate the problem.
 
Upvote 0
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.
 
Last edited:
Upvote 0
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?
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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.
capturewqn.png
 
Upvote 0

Forum statistics

Threads
1,224,502
Messages
6,179,126
Members
452,890
Latest member
Nikhil Ramesh

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top