Tom’s Tutorials For Excel: Random Ramblings

Tom’s Tutorials For Excel: Random Ramblings

Here’s something to think about regarding random numbers in Excel.

Random numbers are based on a predictable algorithm. If you know the previous number and the algorithm, you can predict the next number generated by the random function. This sounds counter intuitive, because random numbers generated by computers are not truly random. A computer uses something called a “random number generator” to produce a series of numbers that satisfy what most people need for randomness, but really, the numbers are not random.

You can test this yourself. Run the following macro on a blank worksheet to see just how “random” random numbers are, and are not. It will produce two columns of 50000 rows each of random numbers. In a truly random environment, what would be the odds of any one pair of rows (that is, for example, the value in cell A32476 equaling the value of cell B32476)? What would be the odds of ALL 50000 pairs equaling each other? You and I would have better luck winning the Powerball lottery jackpot on a day when there’s world peace and the Chicago Cubs winning the World Series.

Sub RandomTest()
Application.ScreenUpdating = False
Dim x As Long
Rnd -1
Randomize 1
For x = 1 To 50000
Cells(x, 1).Value = Rnd
Next x
Rnd -1
Randomize 1
For x = 1 To 50000
Cells(x, 2).Value = Rnd
Next x
Application.ScreenUpdating = True
End Sub