8

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?

|improve this question
  • 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 Apr 24 '14 at 22:36
7

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.

|improve this answer
  • 1
    Shortcuts to do this: Select the range with randoms, press Ctrl+C, press Shift+F10, press V – Alex Frolov Apr 25 '14 at 10:20
4

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.

|improve this answer
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)+a

  • If 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

See also

|improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.