Good day, dear reader!
Recently, the need arose to create a kind of random number generator in Excel within the boundaries of the required task, and it was simple, taking into account the number of people, select a random user, everything is very simple and even banal. But I was interested in 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 a lot of questions, so I will gradually answer them.
So, what exactly can we use this mechanism for:
- Firstly: to test formulas, we can fill the range we need with random numbers;
- Secondly: to generate questions for various tests;
- Thirdly: for any random distribution of tasks in advance among your employees;
- fourthly: for simulating a wide variety of processes;
……and in many other situations!
In this article I will consider only 3 options for creating a generator (I will not describe macro capabilities), namely:
Creating a random number generator using the RAND function
Using the RAND function, we have the ability to generate any random number in the range from 0 to 1 and this function will look like this:
=RAND();
If the need arises, as it most likely does, to use a large random number, you can simply multiply your function by any number, for example 100, and you will get:
=RAND()*100;
But if you don't like fractions or just need to use integers, then use this combination of functions, it will allow you to follow the decimal point or just discard them:
=ROUND((RAND()*100);0);
=RESULT((RAND()*100);0)
When there is a need to use a random number generator in a certain, specific range, according to our conditions, for example, from 1 to 6, you need to 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 the full formula will look like: =RAND()*(6-1)+1, and without fractional parts you need to write: =RESULT(RAND()*(6-1)+1;0)
Create a random number generator using the RANDBETWEEN function
This function is simpler and began to please us in the basic version of Excel, after the 2007 version, which greatly simplified the work with the generator when it is necessary to use a range. For example, to generate a random number in the range from 20 to 50, we will use the following construction:
=RANDBETWEEN(20,50).
Create a generator using the AnalysisToolPack add-on
The third method does not use any generation function, but everything is done using an add-on AnalysisToolPack(This add-in is included with Excel.) The tool built into the table editor can be used as a generation tool, but you need to know that 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 from the list offered by the program and click "OK".
In the window that opens, we select the type from the menu "Distribution", then we indicate additional parameters that change based on the type of distribution. Well, the final step is this indication "Output Interval", exactly the interval where your random numbers will be stored.
And that's all for me! I really hope that I fully explained the question of creating a random number generator and everything is clear to you. I would be very grateful for your comments, as this is an indicator of readability and inspires me to write new articles! Share what you read with your friends and like it!
Don't think too much. This is how you create problems that weren’t there in the first place.
Friedrich Nietzsche
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 a large number of 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 and large, any, as a rule, these are decimal fractions, 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 the 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:Function RAND() returns a uniformly distributed random number x, where 0 £ x< 1. Вместе с тем путем несложных преобразований с помощью функции RAND() you can get any random real number. For example, to get a random number between a And b, just set the following formula in any cell of the Excel table: =RAND()*( b-a)+a .
Note that starting with Excel 2003, the function RAND() has been improved. It now implements the Wichman-Hill algorithm, which passes all standard tests for randomness and guarantees that repetition in a combination of random numbers will begin no earlier than after 10 13 generated numbers.
Random number generator in STATISTICA
To generate random numbers in STATISTICA, you need to double-click on the variable name in the data table (in which you are supposed to write the generated numbers). In the variable specification window, click the button Functions. In the window that opens (Fig. 1.17), you need to select Math and select a function Rnd .
RND(X ) - generation of uniformly distributed numbers. This function has only one parameter - X , which specifies the right boundary of the interval containing random numbers. In this case, 0 is the left border. To fit the general form of the function RND (X ) into the variable specification window, just double-click on the function name in the window Function Browser . After specifying the numeric value of the parameter X need to press OK . The program will display a message indicating that the function was written correctly and will ask for confirmation about recalculating the value of the variable. After confirmation, the corresponding column is filled with random numbers.
Assignment for independent work
1. Generate series of 10, 25, 50, 100 random numbers.
2. Calculate descriptive statistics
3. Construct histograms.
What conclusions can be drawn regarding the type of distribution? Will it be uniform? How does the number of observations affect this conclusion?
Lesson 2
Probability. Simulation of a complete group of events
Laboratory work No. 1
Laboratory work is an independent study followed by a defense.
Lesson objectives
– Formation of stochastic modeling skills.
– Understanding the essence and connection of the concepts “probability”, “relative frequency”, “statistical definition of probability”.
– Experimental verification of the properties of probability and the possibility of calculating the probability of a random event experimentally.
- Formation of skills for studying phenomena of a probabilistic nature.
The events (phenomena) we observe can be divided into the following three types: reliable, impossible and random.
Reliable name an event that is sure to occur if a certain set of conditions is met S.
Impossible an event that is known to not occur if a set of conditions is met S.
Random call an event that, when a set of conditions S is fulfilled, can either occur or not occur.
The subject of probability theory is the study of probabilistic patterns of mass homogeneous random events.
Events are called incompatible, if the occurrence of one of them excludes the occurrence of other events in the same trial.
Several events form full group, if at least one of them appears as a result of the test. In other words, the occurrence of at least one of the events of the complete group is a reliable event.
Events are called equally possible, if there is reason to believe that none of these events is more possible than the others.
Each of the equally possible test results is called elementary outcome.
Classic definition of probability: probability of an event A they call the ratio of the number of outcomes favorable to this event to the total number of all equally possible incompatible elementary outcomes that form the complete group.
A is determined by the formula,
Where m– the number of elementary outcomes favorable to the event A, n– the number of all possible elementary test outcomes.
One of the disadvantages of the classical definition of probability is that it does not apply to trials with an infinite number of outcomes.
Geometric definition probability generalizes the classical one to the case of an infinite number of elementary outcomes and represents the probability of a point falling into a region (segment, part of a plane, etc.).
Thus, the probability of an event A is defined by the formula , where is the measure of the set A(length, area, volume); – measure of the space of elementary events.
Relative frequency, along with probability, belongs to the basic concepts of probability theory.
Relative frequency of the event call the ratio of the number of trials in which the event occurred to the total number of trials actually performed.
Thus, the relative frequency of the event A is determined by the formula where m– number of occurrences of the event, n– total number of tests.
Another disadvantage of the classical definition of probability is that it is difficult to indicate the reasons for considering elementary events to be equally possible. For this reason, along with the classical definition, they also use statistical determination of probability, taking the relative frequency or a number close to it as the probability of an event.
1. Simulation of a random event with probability p.
A random number is generated y y≤ p, then event A has occurred.
2. Simulation of a complete group of events.
Let us number the events that form a complete group with numbers from 1 to n(Where n– number of events) and draw up a table: in the first line – the event number, in the second – the probability of the occurrence of an event with the specified number.
| Event number | … | j | … | n | ||
| Probability of event | … | … |
Let's divide the segment into axis Oy points with coordinates p 1 , p 1 +p 2 , p 1 +p 2 +p 3 ,…, p 1 +p 2 +…+p n-1 on n partial intervals Δ 1 , Δ 2 ,…, Δ n. In this case, the length of the partial interval with number j equal to probability p j.
A random number is generated y, uniformly distributed on the segment. If y belongs to the interval Δ j, then event A j it has arrived.
Laboratory work No. 1. Experimental calculation of probability.
Goals of work: modeling of random events, studying the properties of the statistical probability of an event depending on the number of trials.
We will carry out laboratory work in two stages.
Stage 1. Simulation of a symmetrical coin toss.
Event A consists in the loss of the coat of arms. Probability p events A equal to 0.5.
a) It is necessary to find out what the number of tests should be n, so that with a probability of 0.9 the deviation (in absolute value) of the relative frequency of the appearance of the coat of arms m/n from probability p = 0.5 did not exceed the number ε
> 0: 
.
Carry out calculations for ε = 0.05 and ε = 0.01. For calculations, we use a corollary from the Moivre-Laplace integral theorem:
Where 
; q=1-p.
How are the values related? ε And n?
b) Conduct k= 10 episodes n tests in each. In how many series is the inequality satisfied and in how many is it violated? What will be the result if k→ ∞?
Stage 2. Modeling the implementation of the outcomes of a random experiment.
a) Develop an algorithm for modeling the implementation of an experiment with random outcomes according to individual tasks (see Appendix 1).
b) Develop a program (programs) to simulate the implementation of the outcomes of the experiment a certain finite number of times, with the obligatory preservation of the initial conditions of the experiment and to calculate the frequency of occurrence of the event of interest.
c) Compile a statistical table of the dependence of the frequency of occurrence of a given event on the number of experiments performed.
d) Using the statistical table, construct a graph of the frequency of an event depending on the number of experiments.
e) Compile a statistical table of deviations of the frequency values of an event from the probability of occurrence of this event.
f) Reflect the obtained tabular data on graphs.
g) Find the value n(number of trials) so that and .
Draw conclusions from the work.
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 random 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 places random numbers less than zero. In order 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 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 within a certain range, set the RANDBETWEEN function in the mathematical formulas. Let's set the formulas in column C. The dialog box is filled out like this. 
Let's indicate the smallest and largest 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 by the value.
In Excel, there are several ways to copy formulas so that the references in them do not change. See the description of simple methods for such copying in the article "