However, a random number generator is very useful when working with different scenarios on a dataset or when performing various statistical analyses. A financial model can use a stochastic simulation based on probabilities. The model may need to be run thousands of times, but the random number generator provides the parameters for each simulation. Whatever random numbers you need, Excel has several ways to generate them. In this post, I will show you all the methods you can use to insert random numbers into your workbooks.
Generate random numbers using the RAND function
The first way I will show you is the easiest way to generate random values in Excel. There is a very simple RAND function that takes no parameters and generates a random number between 0 and 1.
Syntax of the RAND function
This function has no required or optional arguments. The function is always entered with an empty parenthesis. This function generates a random decimal number between 0 and 1, but without 0 or 1. Repeated values are possible but unlikely because the RAND function generates numbers from a continuous range of numbers. .The values returned follow a uniform distribution. This means that any number between 0 and 1 will be returned with equal probability.
Generate random numbers between any two numbers
A decimal number between 0 and 1 might not be very useful if you need numbers between 1 and 10. However, you can use a simple formula with the RAND function to generate random numbers between any two numbers. In general, you can create a random number between X and Y using the above formula. For example, to generate numbers between 1 and 10, you can use the formula above. This multiplies the generated random number by 9 and adds 1 to it. This produces decimal numbers between 1 and 10.
Generates random integers between any two numbers
Another possible need you may encounter is to generate random integers between two given numbers. This can also be done with a simple formula. In general, you can use the above formula to generate random integers between two x and y values. For example, the formula above creates random integers between 1 and 10. This is the same formula as above but with the ROUND function to round to zero decimal places. You can copy this formula into the table column, and if you keep pressing F9 to recalculate, you will see several combinations of numbers from 1 to 10. Since the set of possible numbers is discrete, the generated random numbers can be duplicated in list, whichever is the minimum and is the maximum of the range. This also works to create negative numbers. Assuming you need to generate random integers between -3 and 4, then the above formula is what you need. Multiplying the RAND function by 7 produces random numbers between 0 and 7. Add -3 to the result and round to zero decimal places, and you get the random number range of -3 to 4.
Generate random numbers using the RAND BETWEEN function
Excel has a useful feature to generate random numbers within a range of an upper and lower number. This is easier to use than using the RAND function as it includes additional operators to achieve your specific scope.
Syntax of the RAND BETWEEN function
bottom is the bottom range for the values to be returned. top is the top range for the values to return.
Both arguments are required. This function returns random integers between the lower and upper values. This function also returns the upper and lower bound as possible values as long as you are not strictly between them in this function.
Example using the RANDOM BETWEEN function
For example, if you want random numbers between -3 and 4 like in the example above, you can use the formula above.
Generate random numbers using the RANDARRAY function
In general, you don't want a single random value, you want a full set of random values. The RANDARRAY function is the perfect solution for this. It fills a series of cells with a series of random numbers, which can be very powerful. This feature is only available in the Microsoft 365 version of Excel.
RANDARRAY function syntax
rows is the number of rows to return. Column is the number of columns to return. Minis is the minimum value for random numbers. Max is the maximum value for random numbers. Whole_Number is TRUE to return whole numbers and FALSE to return decimal numbers. .
All arguments are optional for this function. If no parameters are included, a single random number with decimals is returned, just like the RAND function.
Example using the RANDARRAY function
To generate a matrix of 4 rows and 3 columns with completely random numbers between 6 and 14, you can use the formula above. This creates an array of values. Notice the blue frame around the numbers? All of this is made from a single formula! If you do not enter a minimum or maximum value, the default value from 0 to 1 is used. The minimum value must be less than the maximum value, otherwise a #VALUE is displayed. Mistake. The array is automatically resized when you change the row or column parameters in the RANDARRAY formula. For this reason they are called dynamic arrays.
There is another method that can be used to enter random numbers without using a formula. You can use a plugin to create random numbers. Excel comes with an Analysis Tool Pak add-in, but you must install it before you can use it.
Install the analysis tools package
Here are the steps to install the Analysis Tool Pak plugin.
Generate random numbers with the analysis tools suite
In the Analysis group, click the Data Analysis button. This shows a pop-up window. Scroll down and select the Random Number Generation option and click OK. A new pop-up window will appear where you can enter your parameters to generate the random numbers. There are several settings that can be adjusted.
Number of Variables This is the number of random number columns you want in your output table. If left blank, all columns in the specified output range will be filled. Number of Random Numbers This is the number of rows of random numbers you want to generate. If left blank, the specified output range will be filled. Distribution You can select different distribution methods from the drop down menu, e.g. B. uniform distribution or normal distribution. Depending on your selection, different options are available in the parameter area. Parameters Enter values to characterize the selected distribution. Random Seed This is optional and is the starting point for the algorithm to generate random numbers. If you use the same seed again, the same random numbers will be generated. If left blank, it takes on the initial value of the timer event. Output range Enter the upper left cell where the table will be created in the worksheet. If you left the variable parameter blank, you must specify a full range. Note that existing data in this area will be overwritten. New Worksheet Layer This option inserts a new worksheet into the workbook and puts the results in cell A1. Enter a sheet name in the adjacent field; Otherwise a default name is used. New Workbook This creates a new workbook and puts the results in cell A1 of the first worksheet.
Press the OK button and Excel will insert the random number according to the selected options. Note that unlike the formula methods shown above, these numbers are hard-coded and will not change when you update the calculations in the workbook.
Generate random numbers with VBA
VBA (Visual Basic for Applications) is the programming language behind the Excel interface and can also be used to generate random numbers. However, it is more complicated than just entering a formula in an Excel cell and you need some programming skills to use it. To open the VBA editor, use the keyboard shortcut Alt + F11. In the left pane of the window (Project Explorer), you can see open workbooks (including add-ins) and available worksheets. From the menu at the top of the window, click Insert, and then click Module. This adds a module window to the current worksheet. Paste or add the following code in the module. Press F5 to run it and Excel will pop up with a random number. Press OK and you will return to the code window. Run the code again and another random number will appear. The random number is between 0 and 1, but contains no values of 0 or 1. You can also assign a parameter to the Rnd function, which is a seed for the starting point of the algorithm used to generate the random numbers. If the initial value is set to a negative number or zero, the same random number will be displayed each time. You can use VBA functions to emulate all the functionality of the front-end methods discussed in this article. For example, if you want to generate completely random numbers between 3 and 10, use the following code above. This code multiplies the random number by 7, then adds 3 and rounds it to zero decimal places. Suppose you want to display your random numbers in the grid. You can do this with the following code. This code uses a For Next loop to iterate through the random number calculation five times and insert the results into a column of cells starting at cell A1. Note that any existing data will be overwritten and no warning or undo is available. Save all previous work first! There is also a VBA function called Randomize. You can use this before the Rnd function to reset the initial value for the timer event or for a specific parameter.
Generate random numbers without duplicates or repetitions
You may have a situation where you want to generate a series of random numbers, but you don't want duplicate values to appear. You may want to select 3 random numbers between the numbers 1-10, each of the 3 selected numbers being unique. You can generate random numbers with the RAND AMONG function and then use Excel's Remove Duplicates function on the ribbon, but you still may not get all the numbers you need. There are several possible solutions available.
Solution using RANK.EQ and COUNTIF functions
If you don't have access to the RANDARRAY function in Excel, you can use a combination of RANK.EQ and COUNTIF to get unique random numbers. You can create your random numbers using RANDBENTWEEN and then use a formula in the next column to sort them and give them a random order from 1 to 10. In cell B2, enter the above formula. Copy this formula so that 10 rows of random numbers go up to cell B11. You will find that some numbers can be duplicated and others are not displayed. You can use the RANK.EQ function to sort them like this: to create a sequence from 1 to 10 but sort randomly. In cell C2, enter the above formula. Note that absolute references (the $ signs) are used so that the formula references remain unchanged when the formula is copied. Copy this formula into cell C11 and this will show all numbers between 1 and 10 but in random order. To explain this formula in more detail, it uses two functions RANK.EQ and COUNTIF.
number is the number whose rank we want to find in the array. Ref is the array in which we want to search for the number. The order is optional and lets you find the range in ascending or descending order. If omitted, ascending order is used.
The RANK.EQ function returns the rank of a number in an array of numbers.
Scope is the range in which to search for instances of the criteria. Criterion is the value that must match within the range.
The COUNTIF function counts the number of cells based on a specific criterion. In this case you count how many times a certain random number has appeared in the list. For each random number, the RANK.EQ function determines its rank position relative to the other random numbers. However, if the random numbers contain duplicates, they create a tied ranking. The COUNTIF function compensates for any ties in rank and adds one to the rank for each time the random number has previously occurred. This creates a unique rank where ties do not get the same rank. Since this range is based on a series of random numbers, the result is the same as randomizing a list of numbers from 1 to 10. Now if you only want 5 non-repeating numbers, you only need to take the top 5 from the sorted list.
Solution with VBA
You can also use VBA to generate a sequence of random numbers from 1 to 10 with no duplicates. This code iterates through values from 1 to 5, generating a random number between 1 and 10 each time. Test the random number to see if it has already been generated. To do this, successful numbers are concatenated into a character string and this character string is examined to determine whether the number has already been used. When found, use the repeat tag to go back and generate a new number again. This is retesting what hasn't been used yet. If it is a new number, it is added to the worksheet.
Solution with dynamic arrays
When you have dynamic arrays in Excel, there is a unique formula method to avoid repetitive values. Suppose you want to return 5 numbers from the sequence 1 to 10. You want each selected number to be unique. This can be done using a combination of the SEQUENCE, SORTBY, RANDARRAY, and INDEX functions. The formula above creates a sequence of numbers from 1 to 10. It then sorts them in random order using the SORT BY function and sorts them into a column of random numbers generated by the RANDARRAY function. The effect is to sort the sequence in random order. Now if you want to get 5 random and unique numbers, just take the first 5 numbers from the randomly sorted sequence. This is exactly what the INDEX function does! This part of the formula returns the first 5 numbers of the randomly sorted sequence.
There are several ways to generate random numbers in Excel. Whether you need integers, decimals, or a set of random numbers with an upper and lower bound, the feature is available. Excel is extremely versatile in this regard. Note, however, that these numbers are pseudo-random numbers generated by an algorithm. Although the random number generator passes all tests for randomness, the numbers are not truly random. number, would have to be driven by some random event occurring outside of the computing environment. For most purposes of creating general simulations and statistical analysis, Excel's random number generator is considered adequate for this purpose. Have you used any of these methods to generate random numbers in Excel? Do you know other methods? Let me know below in the comments!