Excel Random Number (Table of Contents)
Generate Random Numbers in Excel
This function has to be activated by enabling add-ins which we would discuss in this article. But first, let us look at a few examples of the function. The feature provides seven distribution types to generate numbers based on specified parameters. For starters, we open an excel file and click on any cell, then we have to input =RAND(). Excel is a versatile and powerful spreadsheet tool for many analytics tasks. One of these is a statistical analysis of data and it is a market leader in this field. Excel is able to run various statistical functions and one such example is the ability to generate random numbers in Excel workbooks.
This generates a random number as seen below:
This is the most basic form of the RAND function and requires no argument to be written in the parentheses.
How to Generate Random Numbers in Excel?
Generating Random number in Excel is very simple and easy to use. Let us see how to generate a random number in excel with the help of some examples.
We will take a look at more advanced examples in the following few paragraphs.
Generate Random Number in Excel – Example #1
To generate a list of random numbers: We can use the RAND function to generate a list of random numbers in Excel. It can be done by filling the first cell with =RAND() and dragging the fill handle till the cell we want as shown.
We can copy and paste the values to a different column to make the RAND function go away and use the values as is the case with any excel formula.
Generate Random Number in Excel – Example #2
To generate a list of random numbers between specified values: We can use the RAND function to generate a list of numbers between two specified values by using two different modifications of this function as shown below:
- For a random whole number between two integers we use the function RANDBETWEEN:
- For a fractional number between two integers or fractions:
Generate Random Number in Excel – Example #3
To generate a random set of values from a given list: We can use the RAND function to generate a set of values from a given list of strings, in this case, we would pick out random names from a list.
To simplify this, we can change the reference style to default in the Excel menu. The INDEX function is combined with RANDBETWEEN for such cases.
What are RAND and RANDBETWEEN and where can they be useful?
The Excel RAND function can be used to generate a random real number in a uniform distribution of less than 1 and greater than or equal to 0 unless we specify the range. The RANDBETWEEN function always returns a random integer between two specified values.
Both these functions return random numbers with a single distribution. The numbers may or may not be in the upper or lower limit of the requested range. The uses of these functions range from analytics in marketing to quality control and forecasting.
Now that we have seen a few examples of the function RAND and RANDBETWEEN for random number generation, we will look at how to activate the features and enable these functions in Excel.
How to activate and use them?
We start by opening up excel and clicking on the File menu. Then we start by clicking on Options. This takes us to a screen as shown below:
After clicking on Options, we select the Add-Ins menu in the pop-up window that appears.
We select Analysis ToolPak.
After selecting the Analysis ToolPak, we click on Go in the Manage Tab.
This opens another pop-up window. In this screen, we can select only Analysis ToolPak, or select all and click on Ok.
Once we have activated the functionality, we can use it in two ways. One way as shown in the examples would be to use functions and formulae. The other more user-friendly graphical way is to use the Data tab as described below:
- Click on Data in the Excel toolbar.
- Click on Data Analysis on the far left of the toolbar.
- This opens a pop-up window. Select Random Number Generation and click Ok.
- This opens another pop up as shown below:
There are seven types of distribution available in excel for random number generation which are Uniform, Normal, Bernoulli, Binomial, Poisson, Patterned and Discrete distribution types. Any distribution can be chosen depending on the type of data we have. For this exercise, we are going to use Uniform distribution. The following parameters must be specified while using this functionality:
- Number of Variables as this would decide the number of columns used to display the end result.
- A number of Random Numbers we wish to generate.
- The distribution type we want to have for the numbers. This appears as a drop-down menu:
- For Uniform distribution we specify upper and lower limits as shown:
We may use Random Seed, which keeps the data centered on the values entered. This is useful for generating the same set of numbers later.
- The Output Range should also be specified so that excel knows where to place the result. Click Ok.
We will see a result like this:
Conclusion – Generate Random Numbers in Excel
Since the usefulness of random number generation in excel depends a lot of our familiarity with statistics and distribution, the following is a short description of each distributions’ qualities:
- Uniform: Any number between specified high and low values.
- Normal: Mean and Standard deviation within a specified range.
- Bernoulli: Probability of success on a given trial which is either 0 or 1.
- Binomial: Probability of success for a number of trials.
- Poisson: Lambda value which is a fraction equal to 1/Mean.
- Patterned: Numbers have a lower and upper limit, a step, repetition rate for values and a repetition rate for the sequence.
- Discrete: Has a value and probability associated with it, so uses two columns to display the results, the sum of probabilities should be 1.
As discussed above, we see that random number generation in excel is not just a set of random numbers, but like any data, has a pattern to it. It is these patterns that make it such a powerful analytics tool.
Recommended Articles
This has been a guide to Generate Random Numbers in Excel. Here we discuss how to generate random numbers in excel along with practical examples and downloadable excel template. You can also go through our other suggested articles –
