Stop automatically changing value in =RAND or =RANDBETWEEN

  • Thread starter Thread starter RAND
  • Start date Start date
R

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.
 
The function is volatile. The only way to stop it from updating is to
Copy and Edit|Paste Special|Values.

Jerry
 
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.
 
RAND said:
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.
Click to expand...

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.
 
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
 
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
 
I found out that if you select all of your RAND formulas and then go to the formulas tab, at the end press calculation options and select manual, then it should stop producing random numbers:thumb:.
hope this helps
 
Guys there numerous ways to do it. The best way is what Hackmoder_Kat mentioned, to change from automatically to manual the calculations through the Formulas tab. Then you press F9 every time you want a new result.

Another way, if you want to only affect the RAND or RANDBETWEEN function is by creating a circular reference on the same cell. For example: You want a RANDBETWEEN(1;20) you can create at the cell B1 a conditional "IF(A1="go";RANDBETWEEN(1;20); B1)"

You get a warning of a circular reference but it keeps the previous value.
Whenever you want to stop it then you just change the value at A1 into anything other than "go"

If you choose this way you should use the conditional IF for all RAND formulas you use and tie the False with the same cell they are on.

Ta-dah!~
 
RAND said:
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.
Click to expand...

Pick any of the last 2 answers, that's the non-macro or more complicated way to do it- Kudos~!
 
RAND said:
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.
Click to expand...

Hey the easiest way is once you type the formula "=RAND()" or "=RANDBETWEEN()" press "F9" instead of "enter button"
 
Back
Top