Functions for generating random numbers in excel. Create a random number generator in Excel

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

1. The RAND function returns a uniformly distributed random real number. It will be less than 1, greater than or equal to 0.
2. 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.

1. Click on the cell with a random number.
2. In the formula bar, select the formula.
3. Press F9. AND ENTER.

Let's check the uniformity of distribution 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. The formula discards the fractional part.

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 will be able to 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 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 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 "

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 in any cell Excel tables the following formula: =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 enter general view functions RND (X ) into the variable specification window, just double-click on the function name in the window Function Browser . After specifying numerical value 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 independent research followed by protection.

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 probability random event empirically.

- 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 call the ratio of the number of outcomes favorable to this event to the total number of all equally possible incompatible elementary outcomes, forming a 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 infinite number 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 tests.

Another disadvantage of the classical definition of probability is that it is difficult to indicate the reasons for considering elementary events equally possible. For this reason, along with classical definition also enjoy statistical definition probabilities, 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.

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 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 random events, studying properties statistical probability events depending on the number of trials.

Laboratory work We will do it 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 deviation (according to absolute value) relative frequency 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 the corollary from integral theorem Moivre-Laplace:

Where ; q=1-p.

How are the values ​​related? ε And n?

b) Carry out 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 assignments(see appendix 1).

b) Develop a program (programs) to simulate the implementation of the outcomes of a certain experiment final number times, with mandatory saving initial conditions experience 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.