Current

Superseded

Randomisation in Excel

Random numbers that don't repeat, randomly ordered lists and generating lottery numbers

This article looks at:

• The RAND() function
• Producing a list of random numbers without repeats (e.g. lottery numbers)
• Randomising list order

First, let's examine Excel's RAND function. This produces a random number between (and possibly including) 0 and very slightly less than 1. In other words, it can be up to 0.999... for as many digits as Excel will handle, but never 1 itself.

Random, but not really

The numbers are not truly random, but pseudo-random. They are the output of program code that churns out numbers that appear to be random. RAND's output cannot easily be analysed to produce a predictable pattern. And in the long run, values should come up in equal amounts without bias.

For simpler applications, RAND is a good enough simulation of a truly random process. In some situations, however, it is not good enough. This explains why there are third-party alternatives, and why Excel 2003 was kitted out with an upgraded random number generator. Unfortunately, this change introduced a bug that Excel 2003 users should read about here.

The RAND function does not remember its previous output or try to avoid repetition. Any such avoidance would be non-random behaviour.

Using RAND's output

If you want a number outside the range provided by RAND, you must manipulate its output. For example, to get a whole number (integer) between 1 and 9 inclusive, we're looking for one of nine possible results.

1 Multiplying RAND() by nine gives a number in the range 0 to 8.999...
2 Using INT() to cut off the fractional part gives a whole number between 0 and 8.
3 Adding one shifts the range to 1 – 9:

Copy and paste that into a cell, then press [F9] a few times to recalculate. Observe that the results are always a whole number in the desired range.

It's easy to make a mistake scaling and shifting RAND, so always perform a test like this.

The version of Excel I use has a shortcut, the RANDBETWEEN() function. This is in the Analysis ToolPak, an optional install, and is documented in Excel's help file. It produces a random whole number between (and including) the specified limits, for example RANDBETWEEN(1,9).

Under Tools, Data Analysis, there is also a random number tool which you may wish to investigate. Among other refinements, it enables you to produce random numbers distributed in different ways.

Rule out the repeats

As mentioned earlier, RAND (and RANDBETWEEN) do not try to avoid repeating a number.

Raw RAND values aren't so bad. They have a lot of decimal places so the number of possible values is huge. Repetition is possible but infrequent.

Duplicates arise more often when you manipulate and condense RAND values into a relatively small range.

An example of a list of random numbers required to be within a specified range without repeats is a lottery draw simulator. In the real thing, numbers might be on uniquely numbered balls which are pulled from a container without being returned.

Lottery numbers are commonly referred to as "random numbers" but they are not. Once you introduce the idea of non-repetition, they become a set of numbers that can be used up with no further choices possible. Coins, dice, roulette wheels and Brownian particle movements are not like that. You can make as many numbers as you like.

Numbered balls in a lottery machine are like a shuffled deck of cards: a known set of values arranged in random order. A random permutation, in other words, and a different class of beast from what RAND gives us.

And so, it is impossible to simulate lottery number generation and similar processes using simple RAND functions in different cells. They will sometimes produce duplicates.

You could recalculate the sheet until a suitable set of numbers arises, but that is an inefficient way to work. We need a process that reliably produces the required result in one shot.

How it's done

In Excel, I can see two ways to put a set of values in random order.

The first is a quick-and-dirty method suitable for one-offs where tidiness is not an issue:

1 Make a list of all possible values.
2 Make a parallel list of RAND functions.
3 Sort on the RAND functions.

The fact that RAND occasionally repeats does not matter. It is only used as a sort key. You are interested in the parallel list of known values that is being re-ordered.

The screenshots below illustrate the process.

Make a list of values:

Make a parallel list of RAND formulas:

Sort the rows on Column B:

Column A is now randomly ordered:

The second method involves writing a macro to do it automatically. The macro could be written in different ways, either replicating what I did above, or keeping the results of RAND functions inside the code itself, avoiding the need for an extra column.

The VBA macro code below does all the work off-sheet:

Sub NoRepeatsTest()
	Const SIZE_OF_LIST As Integer = 10
	Dim iResults() As Integer
	Dim i As Integer
	Dim iPick As Integer
	Dim iLimit As Integer
	Dim iTmp As Integer

	' Generate an array of available values
	ReDim iResults(SIZE_OF_LIST)
	For i = 1 To SIZE_OF_LIST
		iResults(i) = i
	Next

	' iResults() now contains ordered list of values
	' Prove it by outputting to column A:

	For i = 1 To SIZE_OF_LIST
		Range("A1").Offset(i - 1, 0).Value = iResults(i)
	Next

	'''''''''' Now shuffle iResults() ''''''''''
	' Seed random number generator with system time
	Randomize

	' Set limit 1 below maximum array index
	iLimit = SIZE_OF_LIST - 1

	For i = 1 To SIZE_OF_LIST - 1
		' Pick a random element from index=1 to iLimit
		iPick = Int((iLimit) * Rnd) + 1
    
		' Swap this element with the one above iLimit
		iTmp = iResults(iLimit + 1)
		iResults(iLimit + 1) = iResults(iPick)
		iResults(iPick) = iTmp
    
		' Reduce limit by 1
		iLimit = iLimit - 1
	Next

	' Output iResults() to column B
	For i = 1 To SIZE_OF_LIST
		Range("B1").Offset(i - 1, 0).Value = iResults(i)
	Next
End Sub

I have used a list of values from 1 to 10. Picking, say, the first three, gives you three random non-repeating numbers in the range 1 to 10.

A lottery simulator would work in a similar way, outputting, for example, the first six numbers from a list of 49 shuffled values.

Note that although I have been working with numbers, the method could be applied to lists of text items.

In the Downloads panel on this page you will find an Excel workbook that produces lottery numbers. Apart from having better presentation than the above example, it is customisable in the range and the number of values it outputs. Once the sheet is open, click the Help button.