WRITING AND SOFTWARE BY **IAN SHARPE**

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.

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 offer professional services in software development, databases, data manipulation and writing. Find out more at IanSharpe.com.

site supporter

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.
An obsession with programming diverted me into magazines from an even earlier career in railway civil engineering. That
was in 1986 and for the next 16 years I was a writer and editor on high-profile titles. Some visitors may remember
me from my work a long time ago on leading publications such as *PC Plus*, *PC Answers*, *PC Today*
and *CPC Computing*.

Much of the material here is from that period and does not represent current interests or recent experience. Do not judge me by these digital antiquities!

There is no binding theme, just whatever came into my head to publish. Hence '@random'. There used to be a lot more but I drop items that become too outdated. The remainder survives while it stays popular, which remarkably it does. I may occasionally add new material.

Some of my publishing experiences were exceptionally satisfying and many were great fun. But times changed and the tide turned against the big-circulation magazines of the eighties and nineties.

I had a lingering ambition to write software and some ability, always having done it as an adjunct to my writing, so I switched career again and spent five years as a developer at a busy digital printing company.

Now I work freelance, dividing my time between projects at local technical software and electronics company Dot Software, and whatever else I am engaged in. My professional services site is www.iansharpe.com.

Because my personal site is mostly old material that I'm not very interested in, writing emails about it is not my favourite activity. Even 'quick' questions can take longer to answer than you might expect. It feels rude not to reply and sometimes I will respond, but forgive me if I don't.

If there is a real problem (or opportunity!), my address is **ian@iansharpe.com**.

If you have a few moments, please take a look at MailMyOwner.com.

It would be great to have a link to MailMyOwner on social media, blogs and forums you use. Thank you!

Your download should be visible wherever your browser puts them.