» Restricting the Automatic Recalculation of Volatile Functions

Problem:

When using volatile functions, such as NOW and RANDBETWEEN, the results change whenever changes are made to the worksheet.
Can we restrict the automatic recalculation so that the results will change only on demand?

Solution 1:

Use the IF and RANDBETWEEN functions as shown in the following formula in cell B2:
=IF(A2,RANDBETWEEN(2,12),B2)
Thus, NOW() will automatically recalculate only when the corresponding value in column A is TRUE.
To stop the result from changing, alter the value in column A to FALSE, and the last value calculated will remain unchanged upon recalculation.

Solution 2:

To allow iteration calculations:
Select Tools  Options, check the Iteration box, and press OK.
In column A, next to your original formula, enter either TRUE to enable automatic recalculation or FALSE to disable it.
In place of your original formula in B3 (=NOW()), use the IF and NOW functions as shown in the following formula:
=IF(A3,NOW(),B3)