Throwing Dice with Excel:
Generating random numbers in Excel using only built-in functions and eliminating the volatility issues to produce a more controllable randomness
~~Ray Blake, GR Business Process Solutions

Excel makes it quite easy to generate a random number, and my first attempt at a function to produce what I needed was quite straightforward:

=RANDBETWEEN(1,5)

Nothing too tricky here; this formula produces an integer which is either 1, 2, 3, 4 or 5. The two arguments define the lowest and highest numbers you want to be returned. As formulae go, it's pretty simple. But it has a problem. If you've tried typing it into a worksheet, you may have found the result appeared as:

#VALUE

This is an Excel error code, generated because Excel does not recognise the RANDBETWEEN formula. This is because it is not one of the standard, built-in functions of Excel; rather, it is one of a set of extra functions wrapped up in something called the 'Analysis ToolPak' which Microsoft provides alongside Excel. Most installations of Excel will have it available, but it may not be enabled in every installation.

Here is how to enable it.

1. From the 'Tools' menu, select 'Add-Ins'
2. Find the reference to 'Analysis ToolPak' and put a tick in the box
3. Select 'OK'

And now you're up and running.

But I still had a problem. I was writing my application to be run by other people, on a number of different desktop and laptop PCs. Some might not have the Analysis ToolPak installed, and this was a complication the users could probably do without.

I determined to find a way to achieve the same thing without having to resort to the RANDBETWEEN function. Fortunately, there is a way. One of Excel's built in functions is RAND. It takes no arguments, and simply produces a random number which is a fraction. That is to say, it produces a number which is higher than 0 but less than 1. It takes the form:

=RAND()

Here's the formula I used to turn this into an integer from 1 to 5:

=ROUND(RAND()*(5-1)+1,0)

Here's how this formula works:

1. RAND() produces a random fraction between 0 and 1
2. It is then multiplied by 4 (i.e. 5-1)
3. To the result is added 1
4. It is rounded to the nearest integer, with no decimal places.

Let's follow this through imagining some actual numbers. At step 1, a number from - let's say 0.00001 to 0.99999 is generated. These aren't actually the extremes, but they're near enough. Let's look at each of them over the 4 steps of the calculation.

The ROUND function will always round to the nearest integer. For fractions of less than 0.5, this means it will round down, whilst for 0.5 and above, it performs a rounding up. Here lies the reason why the number we need at step 2 is 4 rather than 5. I've kept it as 5-1 to show the relationship with the maximum number (5) that we want to generate.

This formula can be adapted to work just like RANDBETWEEN on the following basis:

=ROUND(RAND()*([max number]-1)+[min number],0)

Note that the RAND formula, which at first, taking no arguments, appears very limited, can produce far more flexible results than RANDBETWEEN, which looked like the best solution. However, as is often the case in real life, the obvious choice of RANDBETWEEN came with a sting in the tail, requiring an add-in to work.

Now I was there; I had a formula which would generate my random number in all versions of Excel without needing the Analysis ToolPak. But I still had a problem.

RAND is a volatile function. This means that it keeps recalculating, at the drop of a hat. Every time I open, save or close the worksheet, every time the worksheet is recalculated, it gives a new random value. This may be OK for some applications, but for mine this was awkward. I wanted to keep a record of what case study version was used, and with RAND being so volatile, that simply wouldn't work.

What I needed was a way to get a random number calculated on demand, which would then stay as it was until I asked for a different one. In other words, I wanted a dice that I could throw, but which would never be thrown by accident.

Here's how I did it.

In cell B2, I had my formula:

= ROUND(RAND()*(5-1)+1,0)

I started the Macro Recorder, named my new Macro "RandomiseNow" and recorded the following actions:

1. I selected cell B2
2. I copied it to the clipboard using CTRL+C
3. I selected cell B3
4. From the Edit menu, I chose 'Paste Special'
5. I selected the 'Values' option in the Paste dialogue
6. I clicked 'OK'

Cell B3 now contained a number, which was the number created by the formula in Cell B2. But the B3 formula was just a number, not a volatile formula. The worksheet could update any number of times, and although the value in B2 would change, that in B3 would not. (You can test this by forcing a number of worksheet recalculations. Just press F9 repeatedly, and see what happens.)

This is perfect, because it is the figure in B3 which I can then use to look up the case study reference or name, knowing that it will not change when I'm not looking! When a different assessment centre group arrives, I can roll the dice again as it were simply by running the "RandomiseNow" macro. In fact, I put a button on the sheet to run the macro, which is much easier than using the Macro dialogue, particularly for the novice users who would be using my application.

So there we are. We have found how to generate random numbers using the RANDBETWEEN function from the Analysis ToolPak, how to recreate this functionality using only Excel's built-in functions, and finally how to work around the volatility of these functions and control precisely when a random value is updated.

<<<back to contents

Ray Blake lives in England and spent 15 years training people in the financial services industry there. He had always used PCs in his work, and gradually realized he might make a career out of them. He and his business partner set up GR Business Process Solutions (www.grbps.com) which specializes in innovative IT to support knowledge testing and skills assessment. Although he spends a lot of time these days developing in VB and Access, Excel remains his favorite development tool, because, as he says, 'It can do everything; there's no computer application you can think of that you couldn't develop in Excel.'