9
2
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?
Valrok
Posted 2014-04-24T22:28:59.417
Reputation: 241
8
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.
Daniel B
Posted 2014-04-24T22:28:59.417
Reputation: 40 502
1Shortcuts to do this: Select the range with randoms, press Ctrl+C, press Shift+F10, press V – Alex Frolov – 2014-04-25T10:20:54.247
5
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.
Raystafarian
Posted 2014-04-24T22:28:59.417
Reputation: 20 384
1
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
phuclv
Posted 2014-04-24T22:28:59.417
Reputation: 14 930
I don't have Excel installed, and thus can't test this but you should find what you want here. In option 3 he creates his own function which mimics RAND and RANDBETWEEN, but removes the volatility that comes with those functions.
– Michael Frank – 2014-04-24T22:36:01.407