Stop automatically changing value in =RAND or =RANDBETWEEN

[RAND]

Hello, I was wondering how can I make Excel 2003 stop
chaning value everytime I enter the formula =RAND() or
=RANDBETWEEN() in another cell. For example, I put =RAND
() in cell A1, the number came out as 0.1. Then when I
put the formula to generate another number, for example,
putting the formula in cell B1, the value in A1 would
change >_< Is there a way to stop this automatic update
function in Excel 2003? Thank you for reading this.

 

[Jerry W. Lewis]

The function is volatile. The only way to stop it from updating is to
Copy and Edit|Paste Special|Values.

Jerry

 

[Norman Harker]

Hi RAND!

If you want to stop the recalculation permanently, then follow Jerry's
advice and copy > edit> paste special > value > OK

But you can stop it during data entry by setting calculation mode to
manual.
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Harlan Grove]

Hello, I was wondering how can I make Excel 2003 stop
chaning value everytime I enter the formula =RAND() or
=RANDBETWEEN() in another cell. For example, I put =RAND
() in cell A1, the number came out as 0.1. Then when I
put the formula to generate another number, for example,
putting the formula in cell B1, the value in A1 would
change >_< Is there a way to stop this automatic update
function in Excel 2003? Thank you for reading this.

No, there's no way to prevent RAND or RANDBETWEEN refreshing upon each
recalculation. That's what they're designed to do. There are a number of
ways to build random-upon-entry-only functionality, but they all involve
VBA. There's a non-VBA approach that you could try, though it's cumbersome.
When you enter RAND() in a formula, press [F2] to change to Edit mode,
highlight RAND() in the formula, press [F9], press [End] and press [F2]
again to return to Enter mode. If this is too cumbersome, then VBA
programming is unavoidable.

 

[Tushar Mehta]

One easy way is to use iterative calculations. However, that comes
with its own baggage.

To turn on iterative calculations, select Tools | Options... |
Calculation tab. Check on the box for Iteration.

Now, suppose cell E1 controls whether or not a new value is to be
calculated. If it contains FALSE, no change should be made to the
existing value. If it is TRUE, a new function value is desired.

To generate a random number using this technique, in some cell, say C3,
enter the formula =IF(E1,RAND(),C3) Set E1 to TRUE and C3 will contain
a new random number each time Excel recalculates the worksheet. Set E3
to FALSE and the last random number will remain unchanged. Not even a
force full recalculation (CTRL+SHIFT+ALT+F9) will change that value!

To use this technique with today's date, use DATE() instead of RAND()
in the formula in C3.

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[jasonsweeney]

I concur with all that is said. But for those that want to "freeze" a
range of RAND or RANDBETWEEN values, but don't know how to code in VBA,
this is a perfect opportunity to use Excel's record new Macro ability.

A simple example:

(1) In Column C1:C10 enter <=Randbetween(0,9)>

(2) Click on Tools > Macro > Record New Macro

(3) Name your Macro "PasteRands" Click Ok.

(4) Select C1:C10. Click Copy.

(5) Select Cell B1

(6) Click Edit > Paste Special > Click Values > Click Ok.

(7) Click in Cell A1

(8) Click the little Stop Button on the Macro Recorded. Now you have a
Macro that will freeze the random numbers by copying them into range
B1:B10

(9) Now Click View > Toolbars > Forms. Click the button called
"button". Drag and place in your worksheet. Excel will automatically
ask you to assign it to a macro. Select PasteRands. Name your button
"Create Random Numbers".

(10) Finally, if you wish, hide Colum C by right clicking column and
selecting Hide.

-- Cheers