Excel has a function for finding random numbers =RAND(). The ability to find a random number in Excel is an important part 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.
The most common use of this function is to obtain a large number random numbers. Those. You can always come up with 2-3 numbers yourself, for a large number it is easiest to use the function. In most programming languages, a similar function is known as Random (from English random), so you can often find the Russified expression “in random order”, etc. In English excel function RAND is listed as RAND
Let's start with the description of the function =RAND(). This function does not require any arguments.
And it works as follows - it displays a random number from 0 to 1. The number will be real, i.e. by and large any, as a rule it is decimals, for example 0.0006.
Each time you save, the number will change, to update the number without updating, press F9.
Random number within a certain range. Function
What to do if the available 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 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 specify the upper and lower bounds of the values
RANDOMBETWEEN(20;135)
Simple but very convenient!
If you need a lot of cells of random numbers, just stretch the cell below.
Random number with a certain step
If we need to get a random number with a step, for example five, then we will use one of the . This will be UP()
ROUNDUP(RAND()*50;5)
Where we find a random number from 0 to 50 and then round it up to the nearest multiple of 5 from the top. Useful when you are doing the calculation for sets of 5 pieces.
How to use random to validate 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 move this topic into a separate article. Stay tuned for updates this week.
Random number in VBA
If you need to record a macro and you do not know how to do it, then you can read.
VBA uses a function Rnd(), however, it will not work without including the command Randomize to run the random number generator. Let's calculate a random number from 20 to 135 with a macro.
Sub MacroRand() Randomize Range("A24") = Rnd * 115 + 20 End Sub
Paste this code into VBA editor (Alt + F11)
As always, I attach example* with all calculation options.
Write comments if you have questions!
Share our article on your social networks:Good day, dear reader!
Recently, there was a need to create a kind of random number generator in Excel within the boundaries desired task, and it was simple, taking into account the number of people, choose a random user, everything is very simple and even banal. But I was interested, what else can be done with the help of such a generator, what they are, what their functions are used for this and in what form. There are many questions, so I will gradually answer them.
So, what exactly can we use this mechanism for:
- First of all: we can, for testing formulas, fill in the range we need with random numbers;
- Secondly: to form questions of various tests;
- third: for any random distribution of tasks in advance among your employees;
- fourthly: for simulation of various processes;
…… and in many other situations!
In this article, I will consider only 3 options for creating a generator (macro capabilities, I will not describe), namely:
Create a random number generator using the RAND function
With the RAND function, we are able to generate any random number in the range from 0 to 1 and this function will look like this:
=RAND();
If the need arises, and it most likely does, use a random number of great importance, you can simply multiply your function by any number, for example 100, and get:
=RAND()*100;
But if you don't like fractional numbers or just need to use integers, then use this combination of functions, it will allow you after the comma or just discard them:
=ROUND((RAND()*100);0);
=SELECT((RAND()*100),0)
When it becomes necessary to use a random number generator in some specific, specific range, according to our conditions, for example, from 1 to 6, you must use the following construction (be sure to secure the cells with):
=RAND()*(b-a)+a, where,
- a - represents the lower bound,
- b - upper limit
and full formula will look: =RAND()*(6-1)+1, and without fractional parts you need to write: = REV(RAND()*(6-1)+1;0)
Create a random number generator using the RANDBETWEEN function
This function is simpler and started to please us in the basic configuration of Excel, after the 2007 version, which made it much easier to work with the generator when you need to use a range. For example, to generate a random number in the range from 20 to 50, we will use the following construction:
=RANDOMBETWEEN(20,50).
Create a generator using the AnalysisToolPack add-in
In the third method, no generation function is used, but everything is done using the add-on Analysis Tool Pack(This add-in is included with Excel). The tool built into the spreadsheet editor can be used as a generation tool, but you need to know if you want to change the set of random numbers, then you need to restart this procedure.
To gain access to this undeniably useful add-on, you first need to use the dialog box "Add-ons" install this package. If you already have it installed, then the matter is small, select the menu item "Data" - "Analysis" - "Data Analysis", select in the list offered by the program and click "OK".
In the window that opens, we select the type in the menu "Distribution", then specify additional parameters that change based on the type of distribution. Well, the final step is an indication "Exit Interval", exactly the interval where your random numbers will be stored.
And that's all I have! I really hope that the question of creating a random number generator, I opened completely and you understand everything. I would be very grateful for the comments left, as this is an indicator of readability and inspires me to write new articles! Share with your friends read and like!
Don't think too much. So you create problems that were not there in the first place.
Friedrich Nietzsche
To select random data from a table, you need to use function in Excel "Random Numbers". It's ready random number generator in excel.
This feature is useful when performing a spot check or when conducting a lottery, etc.
So, we need to hold a drawing of prizes for buyers. Column A contains any information about buyers - first name, or last name, or number, etc. In the column in we set the function of random numbers. Select cell B1. On the “Formulas” tab in the “Function Library” section, click on the “Math” button and select the “RAND” function from the list. You do not need to fill in 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. For random numbers to be greater than zero, you need to write the following formula. =RAND()*100
When you press the F9 key, the random numbers change. You can choose each time from the list of the first buyer, but change random numbers with the F9 key.
Random number out of rangeExcel.
To get random numbers in certain range, set the function "RANDBETWEEN" to mathematical formulas. Set the formulas in column C. The dialog box is filled in like this. 
We indicate the smallest and most big number. It turned out like this. 
You can use formulas to select from a list with random numbers the names and surnames of buyers.
Attention! In the table, random numbers are placed 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 choose the names of the winners at random from column F. =VLOOKUP(F1;$A$1:$B$6;2;0)
This is the table of winners.
If you need to select winners in several nominations, then press the F9 key and not only random numbers will be replaced, but also the names of the winners associated with them.
How to disable updating random numbers inExcel.
To prevent the random number from changing in the cell, you need to write the formula manually and press the F9 key instead of the Enter key so that the formula is replaced by a value.
In Excel, there are several ways to copy formulas so that the links in them do not change. See description simple ways such copying in the article "
We have a sequence of numbers, consisting of almost independent elements that obey given distribution. Usually evenly distributed.
You can generate random numbers in Excel in different ways and ways. Let's take a look at the best of them.
Random Number Function in Excel
- The RAND function returns a random uniformly distributed 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.
Selecting random numbers with RAND
This function does not require any arguments (RAND()).
To generate a random real number between 1 and 5, for example, use the following formula: =RAND()*(5-1)+1.
The returned random number is evenly distributed 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.
- We click on a cell with a random number.
- Highlight the formula in the formula bar.
- Press F9. AND ENTER.
Let's check the uniformity of the distribution of random numbers from the first sample using the distribution histogram.
The range of vertical values is frequency. Horizontal - "pockets".
RANDBETWEEN function
The syntax of the RANDBETWEEN function is (lower bound; upper bound). The first argument should be less than a second. Otherwise, the function will throw an error. The bounds are assumed to be integers. The formula discards the fractional part.
An example of using the function:
Random numbers with 0.1 and 0.01 precision:
How to make a random number generator in Excel
Let's make a random number generator with the generation of 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 with a step of 10.
From the list text values you need to choose 2 random. 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 functions RAND and RANDBETWEEN produce random numbers with a single distribution. Any value with the same probability can fall into the lower limit of the requested range and into the upper one. It turns out a huge spread from the target value.
Normal distribution means that most of the generated numbers are close to the target. Correct the RANDBETWEEN formula and create an array of data with normal distribution.
The cost of goods X is 100 rubles. The entire batch produced is subject to a normal distribution. The 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 build a graph with a normal distribution for standard deviation 1.5 rubles.
We use the function: =NORMINV(RAND();100;1.5).
Excel calculated which values are in the range of probabilities. 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 the rest.
Let's move on to plotting. 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 form a diagram with a normal distribution. The value axis is the number of variables in the interval, the category axis is the periods.
