, , , , , , , , , , / /

Truly random numbers are possible in Excel

With Random.org you can get truly random numbers for Excel plus options not available in the spreadsheet program like random but unique numbers with no repeats and random dates.

Using ‘True’ Random numbers in Excel

Our article on Random Numbers in Excel continues to surprise us by being one of the most popular pages on our web site.

While the RAND() RandBetween() and now RandArray() functions in Excel are sufficiently ‘random’ or pseudo-random for most purposes there are situations where it isn’t really enough.

You might want truly random numbers, not those generated by a mathematical formula like that in Excel. Usually this only applies to very large uses to a high precision when an improperly random distribution could affect the results.

There are other needs for truly random numbers that us mere mortals are likely to need, so this article will cover some of these. We’ll also tell you about a source of random numbers or lists which would be difficult to get from Excel alone.

We’ll tell you about a place to get truly random numbers, insert them into Excel and use them for common purposes.

Random.org

We’ll be showing you some of the nifty features of Random.org – a mostly free service which generating single time use random numbers.

Instead of using a formula to generate ‘fake’ randomness, Random.org uses an external data source as an input, and applies some fearsome maths to produce a truly random sequence.

The numbers are random because the primary source of the numbers is background radio noise. Random.org has 4 radios tuned to different non-broadcast frequencies to provide the ‘raw’ data for the site.

You can see a full description http://random.org/randomness/ the net result is a set of ‘True Random Numbers’ that can’t be repeated (except by chance).

There is a limit of one million bits per IP address with regular ‘top ups’ – this should be more than enough for most people.

Fixed random numbers

Each time Excel re-calculates the worksheet the results of Rand() are changed which is normally what is needed but not always.

For example you might want to show a random roll of a virtual dice (ie integer from 1 to 6) but keep the result of that ‘roll’ once made.

The only way to do that in Excel is to insert a formula with Rand() then convert the result into an unchanging number. Do that by copying the cell then Paste it using the ‘Values only’ Paste option.

Random.org has a specific ‘Dice’ web page which will return a number from 1 to 6.

Go to http://random.org/dice/ and select the number of dice to roll.

Unfortunately for Excel users, the results are images which can’t be copied as numbers into Excel.

There is also a ‘coin toss’ page http://random.org/coins/ which has a clever selection of ancient, modern and novelty coins.

You can get results for both dice and coin toss that can be copied into Excel, see below.

Repeating random numbers

In the ‘dice’ example, each of the digits can be repeated (ie the ‘dice’ can roll the same number more than once).

For random lists for wider ranges and more results you can go to http://random.org/integers/?mode=advanced (or http://random.org/integers/ and click on Advanced Mode). Copy the generated list into a column in Excel.

Tip: you can use the Integers page to create numbers for Excel to emulate the dice and coin toss options. Choose integer ranges of 1 – 6 or 0 – 1 respectively.

Which format?

In most Random.org pages there’s an Advanced Mode which we prefer for copying to Excel.

Choose the Output Format: As a bare-bones text document (type text/plain) and a single column.

With that option the results can be selected easily with Ctrl + A then Copy.

Random list

Random.org lets you enter a list of items for it to shuffle into a random order http://random.org/lists/

This is fine for short lists but not so good for longer ones.

In addition, there’s a privacy issue. As the maker of Random.org notes, he’d prefer not to have access to any private list of names etc, even if on a transient basis.

However there is a way to use Random.org data but keep the list itself on your copy of Excel …

Random ordering

A very common use of random numbers is some type of random selection.

  • Drawing a lottery
  • Choosing staff or students for a random test
  • Choosing the order of people to attend some event or meeting

In all these cases you need all the integers between two values (like 1 to 100) in a random order. All the integers in the range have to be included with no repeats.

Excel can’t do this directly (the RandBetween() function can generate repeats). You could write a complex worksheet to do this but Random.org provides an easier source.

  • Go to the Sequence Generator http://random.org/sequences/
  • then choose the Advanced option
  • enter the range of numbers you need
  • choose a plain text list (its easier to paste into Excel)
  • choose a new random series or some random series generated in the past.
  • Copy the entire generated series (click on the web page, then Ctrl + A, then right-click and choose Copy).
  • Paste into a new column in Excel next to the list of names, ticket numbers etc.
  • Sort the Excel worksheet by the random sequence to see the list in a new, random, order.

For example if you have sold 95 tickets in a raffle then ask for a random series from 1 to 95.

Or you might want random numbers to match the row numbers. If there’s a heading on the list the rows with data might go from row 2 and upwards. Use Random.org to produce a random series from 2 to the number of the last used row.

Another advantage of this method is that the random numbers are ‘fixed’ and unchanging in your worksheet. You can check the list anytime to see that all the names or numbers where included.

To make a list of people to interview randomly, put the list of people in one column then generate a sequence from Random.org (from 1 to the number of people) and copy it into a neighbouring column.

Then you can sort the list by the random number column to see the order of people.

Display and Print options

It’s even possible to print out the worksheet and post it so that everyone can see that all the names or ticket numbers were included and that the selection was truly random. You could print the list twice, once in order of random numbers (with ‘1’ at the top) and also in name or ticket number order for easy reference. Producing a complete list makes the selection method more open and transparent to the participants.

Normally you’d consider the ‘winners’ of a random selection to be the ones that get the lowest numbers assigned to them (eg those with random numbers 1 to 5 or 1 to 10) but you can get more creative when you have a full list produced in Excel. The ‘winners’ could be counted from the highest down (eg 100 to 95) or the top and bottom (eg 1 to 5 and 100 to 95) or a mix (the top five, the bottom five and five in the exact middle).

Random Time lists

For a list of random times use http://random.org/clock-times/ with the same plain text option mentioned above. Copy the generated list into Excel.

Use this to make a random list of times, for example when to take a sample during the day.

Random Date Lists

Random.org doesn’t have a system to produce random dates but it can be done.

As we’ve mentioned in the past, Excel calculates dates using an ordinal number For example 39187 is displayed by Excel as 15 April 2007 when in Date format.

You can see the ‘raw’ number instead of a date simply by changing the format of an Excel cell from Date to Number.

To generate a random list of all the dates in 2007 get Random.org’s Sequence Generator to make a list from 39083 to 39477.

Copy the list into Excel, then change the format of the cells to Date so they’ll appear as dates.

If you need a random list of dates with repeats use the Integer Generator instead.

Join Office for Mere Mortals today

Office for Mere Mortals is where thousands pick up useful tips and tricks for Word, Excel, PowerPoint and Outlook.

Give it a try. You can unsubscribe at any time.  We've never spammed or sold addresses since we started over twenty years ago.
Invalid email address