Filtering random numbers without regenerating random numbers

This may be a simple problem, but I am using formulas that generate random numbers. I then want to filter the spreadsheet to hide or remove negative numbers, but when I apply the filter the formulas produce new randomn numbers. How can I hide or even delete the rows where the formula produces a negative number without triggering new randomization?

I found I needed persisted and/or reproducible random number sequences all the time. Turns out this is possible using only standard spreadsheet formulas.

One way is to use the so called Lehmer random number method. It generates a sequence of random numbers in your spreadsheet that stays the same until you change the "seed number", a number you choose yourself and will recreate a different random sequence for each seed number you choose.

The short version:

1. In cell B1, enter your "seed" number, it can be any number from 1 to 2,147,483,647
2. In cell B2 enter the formula =MOD(48271*B1,2^31-1) , this will generate the first random number of your sequence.
3. Now copy this cell down as far as the the random sequence you want to generate.

That's it! If you want a different set of numbers, just change the seed number in B1. If you ever want to recreate the same set of numbers again, just use the same seed and the same random sequence will appear.

Note: The random numbers will be in the range 1 to 2,147,483,647. To normalize them to the range 0 to 1 as the results of the built-in RAND function, go to cell C2 and enter the formula =B2/(2^31-1). Then fill that formula down as far as your random number sequence in column B.