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)
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.
I am trying to create a name generator using the =RANDBETWEEN() function but since it is volatile, I am trying to restrict the function and I cannot figure it out. Can you expand on what you're stating and also possibly provide a picture of an excel page? Thank you