Unique Random Number Generation

Introduction

It is very common to see someone asking the question of how we might generate random numbers in Excel that do not repeat. You will find lots of answers on the internet: do this, do that, but Excel's numbers are not truly random ...

Read this page and ease your aching heart!

Three Random Number Functions

In general, I find that any function or formula that generates random numbers that contain decimal numbers will provide you with unique results, as opposed to integers. The following are the three easiest to use and probably best known random number functions

• RAND() … with decimals
• RANDBETWEEN() … integers
• RANDARRAY() … can be integers or decimals, you choose

Random Number Generator

In addition to these functions, the Data Analysis ToolPak has a Random Number Generator that will generate numbers according to these distributions:

• Uniform … with decimal
• Normal … with decimal
• Bernoulli … integer
• Binomial … integer
• Poisson … integer
• Patterned … integer
• Discrete … could be with decimals or integer, you decide

I created a worksheet that generates random numbers according to the above:

Click to enlarge the image

And after pressing the F9 a few times and recording my analysis each time, I got this kind of result:

Click to enlarge the image

This tells us that generating DECIMAL random numbers using

• RAND()
• RANDARRAY()
• Uniform
• Normal
• Patterned

All returned unique results although the Patterned method did not return the 40 values I was expecting.

Summary

In summary, I would say that you can go and do a google search for random number generation in Excel and you can find long winded, sometimes complex, methods to help you to do that. However, just use RAND() or RANDARRAY() with decimals and that is all you need.

I know, someone is going to tell you that Excel’s random numbers are not truly random but how critical is that to your question? Probably not.

Duncan Williamson

12th August 2022