How to fix the random numbers generated by Excel rand()

Question

I am using =rand() to generate a lot of random number. Everytime, I make some changes in the file, it will generate a whole new set of numbers. I want to keep the numbers random, but keep them fixed everytime. How to do that?

Turn off auto-calculation of formulas. – Phylogenesis Apr 20 '18 at 11:41 — Phylogenesis, Apr 20 '18 at 11:41
copy what numbers you got and paste values only – krib Apr 20 '18 at 11:42 — krib, Apr 20 '18 at 11:42

Vityata · Answer 1 · 2018-04-20T14:06:38.967

Rand() is a volatile funciton. This means that it is recalculated every time Excel recalculates. Pretty much you have three options (2 mentioned in the comments):

set the calculations to manual (this is dangerous, you may forget it later);
remove the formula and save the values (this will work quite ok);
make a User Defined Function, which does exactly the same and is non-volatile:

Public Function RandNotUpdate() As Double
    RandNotUpdate = Rnd
End Function

How to fix the random numbers generated by Excel rand()

1 Answers1