controlling when random numbers are recalculated in excel?

Question

I have an Excel 2003 workbook with approximately 80,000 random numbers. Each of those has about 10 more cells that are depend upon the random number. So every time I make any change to anything, excel recalculates 800,000 cells. It takes about a minute to complete a recalculate.

Is there an option somewhere that would restrict when random numbers get redone without interfering with other recalculations?

The only thing I've seen so far is to to put a conditional around the rand function (e.g. =if($A$1,rand(),B1) so that random numbers only take effect when cell A1 is true. Unfortunately, this method wouldn't really work for what I am trying to do.

? · Accepted Answer

I don't think you can selectively turn off calculation of random numbers. Turning off recalculation all together can be done, but is risky. At some point, someone will take data from the sheet without a recalculation.

How about picking your random numbers from a separate list that is recalculated using a VBA macro? Create a new sheet, with basically a long list of "random numbers" - you could use the RAND function to create it, then copy and paste as values to make it just text - which matches the layout of your main sheet. Then, say, cell C1 on your main sheet points to cell C1 on your "random number" sheet. Next, create a basic macro that loops through all the cells on the random number sheet that you are using, and puts a random number in each one as a value. That way, the random number list is only updated when you execute the macro

? · Answer

Rand In Excel

Donut Tim · Answer

Instead of automatic calculation you can select manually calculation.
Then it will only calculate when told to do so by a macro or manually if you press F9 to recalculate.

In Excel 2010 this located: FILE > OPTIONS > FORMULAS > CALCULATION OPTIONS

In Excel 2003 you should be able to find it in a similar area or use HELP to find it.
.

controlling when random numbers are recalculated in excel?

3 Answers