Yahoo Answers is shutting down on May 4th, 2021 (Eastern Time) and beginning April 20th, 2021 (Eastern Time) the Yahoo Answers website will be in read-only mode. There will be no changes to other Yahoo properties or services, or your Yahoo account. You can find more information about the Yahoo Answers shutdown and how to download your data on this help page.
Trending News
controlling when random numbers are recalculated in excel?
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.
3 Answers
- JohnLv 68 years agoFavorite 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
- Donut TimLv 78 years ago
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.
.
