Andea S. , a student in Learning Tree’s course Power Excel: Analyzing Data to Make Business Decisions, expressed dismay about a problem at work. When she attempted to plot the results of her Monte Carlo inventory and production model, her entire set of model values would change. No surprise here; functions that generate random numbers are volatile, and will be recalculated whenever the worksheet recalculates.
Going to the formulas tab and setting the Calculation Options to “Manual” will not, by itself, help. Setting sheet calculations to manual postpones recalculations; it does not turn them off. If we are working with a large complex workbook, we may not wish to have time-consuming recalculations performed every time we make a change. However, the recalculations must occur eventually, or we would risk inconsistencies. If we had cells indicating that 1 + 1 = 2, and we changed a “1” to a “3”, we would not want our worksheet to retain 1 + 3 = 2 indefinitely. If you do not manually initiate a recalculation, Excel will recalculate when you save.
Sometimes when you enter a formula in a cell, you do not wish to retain the formula: you only care about the resulting value. If you hit <F9> instead of <enter> Excel will insert into the cell only the value. As convenient as this can be, it only works for a single cell. What if we have a large range of cells containing formulas and we would like to replace the cell content with the corresponding values. The trick given in many books and blogs is to highlight the range, copy the range using <ctrl>C and then immediately right-clicking the still-selected ranging and choosing the Paste Values icon, second from the left in the Paste Special section of the context menu.
This will not work for formulas involving the generation of random values. Ironically, it is a double-whammy; when you paste, both the values you copied and the values you paste will be recalculated independently. You simply cannot copy random values in this way.
The trick is to combine setting recalculate to Manual with the copy-and-paste-values method. When the calculation option is set to Manual, pasting values does not trigger a recalculation. So you must set calculation to Manual, do the in-place copy-and-paste, and then return the Calculation back to automatic (something I always forget to do).
Remember that once you copy-and-paste values, the formulas are gone! You might wish to create a copy of the original worksheet with formulas intact before changing all the cells to values!
After generating an ensemble of Monte Carlo models, it is often desirable to “freeze” worksheet values from further change. Neither copy-and-paste nor setting recalculation can accomplish this by themselves, but together they can save an unchanging set of values.