How to keep random numbers from changing in Excel?
I am trying to generate a set list of random numbers between 1 and 5 using the RANDBETWEEN formula.
I'm running into a problem that every time I click another cell anywhere in the sheet or even open and close the sheet after saving it, the random numbers change again. How can I keep the random numbers from changing?
Copy the range and insert at the same position. After inserting, press Ctrl and select the option to only keep actual values from the menu that opens.
RANDBETWEEN() is regenerated every time the sheet is calculated so the only way to keep the random values persistent is to turn off automatic calculations or to copy the values that are generated and store them.
You can either overwrite where they were calculated or place them in another location by copying and paste special - values.
MS has explained the way to do this in RAND function's documentation
To generate a random real number between a and b, use:
=RAND()*(b-a)+aIf you want to use RAND to generate a random number but don't want the numbers to change every time the cell is calculated, you can enter
=RAND()in the formula bar, and then press F9 to change the formula to a random number. The formula will calculate and leave you with just a value.
So you can enter =RAND()*(b-a)+a or =RANDBETWEEN(a, b) then press F9 to replace the formula with its result
See also
- How do I "Freeze" the numbers given by RANDBETWEEN?