Random numbers are often useful in spreadsheets. For example, you can fill a range with random numbers to test formulas, or generate random numbers to simulate a wide variety of processes. Excel provides several ways to generate random numbers.
Using the RAND function
Function provided in Excel RAND generates a uniform random number between 0 and 1. In other words, any number between 0 and 1 has equal probability be returned by this function. If you need random numbers with large values, use simple formula multiplication. The following formula, for example, generates a uniform random number between 0 and 1000:
=RAND()*1000 .
To restrict the random number to integers, use the function ROUND:
=ROUND((RAND()*1000);0) .
Using the RANDBETWEEN function
To generate uniform random numbers between any two numbers you can use the function CASE BETWEEN. The following formula, for example, generates a random number between 100 and 200:
=RANDBETWEEN(100,200) .
In versions earlier than Excel 2007, the function CASE BETWEEN Available only when installing an additional analysis package. For backward compatibility (and to avoid using this add-on), use a formula like this: A represents the bottom, a b - upper limit: =RAND()*(b-a)+a. To generate a random number between 40 and 50, use the following formula: =RAND()*(50-40)+40 .
Using the Analysis ToolPack Add-in
Another way to get random numbers in a worksheet is to use the plugin Analysis ToolPack(which came with Excel). This tool can generate uneven random numbers. They are not generated by formulas, so if you need a new set of random numbers, you need to rerun the procedure.
Get access to the package Analysis ToolPack by selecting Data Analysis Data Analysis. If this command is missing, install the package Analysis ToolPack using the dialog box Add-ons. The easiest way to call it is to press Atl+TI. In the dialog box Data Analysis select Random number generation and press OK. A window will appear as shown in Fig. 130.1.
Select the distribution type from the drop-down list Distribution, and then set additional parameters (these vary depending on the distribution). Don't forget to specify the parameter Output interval, which stores random numbers.
Excel has a function for finding random numbers =RAND(). The ability to find a random number in Excel is an important component of planning or analysis, because you can predict the results of your model on a large amount of data, or just find one random number to test your formula or experience.
Most often this function is used to obtain large quantity random numbers. Those. You can always come up with 2-3 numbers yourself; for a large number it’s easiest to use a function. In most programming languages, a similar function is known as Random (from English random), so you can often come across the Russified expression “in random order”, etc. In English Excel, the RAND function is listed as RAND
Let's start with a description of the function =RAND(). This function requires no arguments.
And it works as follows: it outputs a random number from 0 to 1. The number will be real, i.e. By by and large any, usually this decimals, for example 0.0006.
Each time you save the number will change; to update the number without updating, press F9.
A random number within a certain range. Function
What to do if the existing range of random numbers does not suit you, and you need a set of random numbers from 20 to 135. How can this be done?
You need to write down the following formula.
RAND()*115+20
Those. a number from 0 to 115 will be randomly added to 20, which will allow you to get a number in the desired range each time (see the first picture).
By the way, if you need to find an integer in the same range, there is a special function for this, where we indicate the upper and lower bounds of the values
RANDBETWEEN(20,135)
Simple, but very convenient!
If you need multiple random number cells just drag the cell below.
Random number with a certain step
If we need to get a random number in increments, for example five, then we will use one of. This will be OKRUP()
AROUNDTOP(RAND()*50,5)
Where we find a random number from 0 to 50 and then round it up to the nearest multiple of 5. Handy when you're doing the calculation for sets of 5.
How to use random to test a model?
You can check the invented model using a large number of random numbers. For example, check whether a business plan will be profitable
It was decided to include this topic in a separate article. Stay tuned for updates this week.
Random number in VBA
If you need to record a macro and you don’t know how to do it, you can read.
VBA uses the function Rnd(), but it will not work without enabling the command Randomize to run the random number generator. Let's calculate a random number from 20 to 135 using a macro.
Sub MacroRand() Randomize Range("A24") = Rnd * 115 + 20 End Sub
Paste this code into VBA editor (Alt + F11)
As always, I apply example* with all payment options.
Write comments if you have questions!
Share our article on your social networks:We have a sequence of numbers consisting of almost independent elements who obey given distribution. As a rule, uniform distribution.
You can generate random numbers in Excel in different ways and ways. Let's consider only the best of them.
Random Number Function in Excel
- The RAND function returns a uniformly distributed random real number. It will be less than 1, greater than or equal to 0.
- The RANDBETWEEN function returns a random integer.
Let's look at their use with examples.
Sampling random numbers using RAND
This function requires no arguments (RAND()).
To generate a random real number in the range from 1 to 5, for example, use the following formula: =RAND()*(5-1)+1.
The returned random number is distributed uniformly over the interval.
Each time the worksheet is calculated or the value in any cell in the worksheet changes, a new random number is returned. If you want to save the generated population, you can replace the formula with its value.
- Click on the cell with a random number.
- In the formula bar, select the formula.
- Press F9. AND ENTER.
Let's check the uniformity of the distribution of random numbers from the first sample using a distribution histogram.
The range of vertical values is frequency. Horizontal - “pockets”.
RANDBETWEEN function
The syntax for the RANDBETWEEN function is (lower bound; upper limit). The first argument must be less than the second. Otherwise the function will throw an error. The boundaries are assumed to be integers. Fractional part the formula is discarded.
Example of using the function:
Random numbers with precision 0.1 and 0.01:
How to make a random number generator in Excel
Let's make a random number generator that generates a value from a certain range. We use a formula like: =INDEX(A1:A10,INTEGER(RAND()*10)+1).
Let's make a random number generator in the range from 0 to 100 in steps of 10.
From the list text values you need to choose 2 random ones. Using the RAND function, we compare text values in the range A1:A7 with random numbers.
Let's use the INDEX function to select two random text values from the original list.
To choose one random value from the list, apply the following formula: =INDEX(A1:A7,RANDBETWEEN(1,COUNT(A1:A7))).
Normal distribution random number generator
The RAND and RANDBETWEEN functions produce random numbers with a uniform distribution. Any value with the same probability can fall into the lower limit of the requested range and into the upper one. This results in a huge spread from the target value.
A normal distribution implies that most of the generated numbers are close to the target number. Let's adjust the RANDBETWEEN formula and create a data array with normal distribution.
The cost of product X is 100 rubles. The entire batch produced follows a normal distribution. A random variable also follows a normal probability distribution.
Under such conditions, the average value of the range is 100 rubles. Let's generate an array and plot a graph with a normal distribution at standard deviation 1.5 rubles.
We use the function: =NORMINV(RAND();100;1.5).
Excel calculated which values were within the probability range. Since the probability of producing a product with a cost of 100 rubles is maximum, the formula shows values close to 100 more often than others.
Let's move on to plotting the graph. First you need to create a table with categories. To do this, we divide the array into periods:
Based on the data obtained, we can generate a diagram with a normal distribution. The value axis is the number of variables in the interval, the category axis is periods.
To select random data from a table, you need to use function in Excel “Random numbers”. This is ready random number generator in Excel.
This function is useful when conducting a spot check or when conducting a lottery, etc.
So, we need to hold a prize draw for customers. Column A contains any information about customers - first name, last name, number, etc. In column c we set the random number function. Select cell B1. On the “Formulas” tab in the “Function Library” section, click on the “Mathematical” button and select the “RAND” function from the list. There is no need to fill out anything in the window that appears. Just click on the “OK” button. Copy the formula by column. It turned out like this.This formula puts random numbers less than zero. So that the random numbers are greater than zero, you need to write the following formula. =RAND()*100
When you press the F9 key, the random numbers change. You can select the first buyer from the list each time, but change random numbers with the F9 key.
Random number from a rangeExcel.
To get random numbers in a certain range, set the RANDBETWEEN function to mathematical formulas. Let's set the formulas in column C. The dialog box is filled out like this. 
Let's indicate the smallest and most large number. It turned out like this. 
You can use formulas to select first and last names of customers from a list with random numbers.
Attention! In the table, we place random numbers in the first column. We have such a table.
In cell F1 we write a formula that will transfer the smallest random numbers.
=SMALL($A$1:$A$6,E1)
We copy the formula to cells F2 and F3 - we select three winners.
In cell G1 we write the following formula. She will select the names of the winners using random numbers from column F. =VLOOKUP(F1,$A$1:$B$6,2,0)
The result is a table of winners.
If you need to select winners in several categories, then press the F9 key and not only will the random numbers be replaced, but also the names of the winners associated with them.
How to disable random number updating inExcel.
To prevent a random number from changing in a cell, you need to write the formula manually and press the F9 key instead of the Enter key so that the formula is replaced with the value.
In Excel, there are several ways to copy formulas so that the references in them do not change. See description simple ways such copying in the article "
