WRITING AND SOFTWARE BY IAN SHARPE
This article looks at:
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.
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.
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:
= 1 + INT( 9 * RAND() )
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.
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.
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.
Version 3.0, 7 April 2010
Compatibility Tested on Excel 2000 & 2002
Description / note Lottery number generator. Configurable for different lotteries, v3 works with one or two sets of balls. Ensure macros are enabled.
Version 2.0, June 2005
Compatibility Tested on Excel 97, 2000, 2002
Description / note Works for lotteries with one set of balls. Ensure macros are enabled.
I don't try to make money from this site. It's old stuff that I generally do not maintain. You are welcome to anything useful or interesting you find here.
If you want to say thanks, please take a look at MailMyOwner.com, which I have an interest in.
It's a lost-and-found service that aims to unite people with lost property and pets by labelling them with a personal ID code.
It would be great to have links to MailMyOwner on any social media and suitable forums you use.
This is the personal web site of Ian Sharpe, a software developer and writer based in Bath, United Kingdom.
Before taking up software development full time, I had a career in computer magazine publishing. My obsession with programming accidentally took me into magazines in 1986 and I worked in the industry for 16 years. Some visitors may remember me from titles such as PC Plus, PC Answers, PC Today and CPC Computing.
Most of the material here dates from that period.
There is no binding theme – it's just whatever comes into my head to publish, hence '@random'.
There used to be a lot more, but I drop items when they become outdated and do not reflect current interests.
The remainder survives while it stays popular. I may occasionally add new material.
When my magazine adventure eventually played out, I switched to what I wanted to do all those years ago. I spent five years developing software at a busy digital printing company.
Now I work freelance. My professional services site is www.iansharpe.com.
Because my personal site is mostly old material that I'm not very interested in, answering email about it is not my favourite activity. If there is a real problem (or opportunity!), my address is email@example.com.