Random from 1 to 12. Random number generator

Note that, ideally, the distribution density curve of random numbers would look like the one shown in Fig. 22.3. That is, in the ideal case, the same number of points fall into each interval: N i = N/k , where N total number of points, k number of intervals, i= 1, ½, k .

It should be remembered that the generation of an arbitrary random number consists of two stages:

• generating a normalized random number (that is, uniformly distributed from 0 to 1);
• transformation of normalized random numbers r i into random numbers x i, which are distributed according to the (arbitrary) distribution law required by the user or in the required interval.

Random number generators according to the method of obtaining numbers are divided into:

• physical;
• tabular;
• algorithmic.

Physical RNG

Examples of physical RNGs are: a coin (“eagle” 1, “tails” 0); dice; a drum with an arrow divided into sectors with numbers; hardware noise generator (GS), which is used as a noisy thermal device, for example, a transistor (Fig. 22.422.5).

Tabular RNG

Tabular RNG as a source of random numbers use specially compiled tables containing verified uncorrelated, that is, numbers that do not depend on each other in any way. In table. 22.1 shows a small fragment of such a table. Walking the table from left to right from top to bottom, you can get random numbers evenly distributed from 0 to 1 with the desired number of decimal places (in our example, we use three decimal places for each number). Since the numbers in the table do not depend on each other, the table can be traversed in different ways, for example, from top to bottom, or from right to left, or, say, you can select numbers that are in even positions.

The advantage of this method is that it gives truly random numbers, since the table contains verified uncorrelated numbers. Disadvantages of the method: a lot of memory is required to store a large number of digits; great difficulties in generating and checking such tables, repetitions when using the table no longer guarantee the randomness of the numerical sequence, and hence the reliability of the result.

There is a table containing 500 absolutely random verified numbers (taken from the book by I. G. Venetsky, V. I. Venetskaya "Basic Mathematical and Statistical Concepts and Formulas in Economic Analysis").

Algorithmic RNG

The numbers generated using these RNGs are always pseudo-random (or quasi-random), that is, each subsequent generated number depends on the previous one:

r i + 1 = f(r i) .

Sequences made up of such numbers form loops, that is, there is necessarily a cycle that repeats an infinite number of times. Repeating cycles are called periods.

The advantage of RNG data is speed; generators practically do not require memory resources, are compact. Disadvantages: the numbers cannot be fully called random, since there is a dependence between them, as well as the presence of periods in the sequence of quasi-random numbers.

Consider several algorithmic methods for obtaining RNG:

• method of middle squares;
• method of middle products;
• mixing method;
• linear congruent method.

Mean square method

There is some four-digit number R 0 . This number is squared and entered into R one . Coming from R 1 the middle (four middle digits) is taken a new random number and written into R 0 . Then the procedure is repeated (see Fig. 22.6). Note that in fact, as a random number, it is necessary to take not ghij, a 0.ghij with a zero and a decimal point appended to the left. This fact is reflected in Fig. 22.6, and in subsequent similar figures.

Disadvantages of the method: 1) if at some iteration the number R 0 becomes zero, then the generator degenerates, so the correct choice of the initial value is important R 0; 2) the generator will repeat the sequence through M n steps (at best), where n word length R 0 , M the base of the number system.

For an example in fig. 22.6 : if number R 0 will be represented in the binary number system, then the sequence of pseudo-random numbers will repeat after 2 4 = 16 steps. Note that the repetition of the sequence can occur even earlier if the initial number is chosen unsuccessfully.

The method described above was proposed by John von Neumann and dates back to 1946. Since this method proved unreliable, it was quickly abandoned.

Method of median products

Number R 0 multiplied by R 1 , from the result R 2 the middle is removed R 2 * (this is another random number) and multiplied by R one . According to this scheme, all subsequent random numbers are calculated (see Fig. 22.7).

Mixing method

The shuffling method uses operations to rotate the contents of a cell left and right. The idea of ​​the method is as follows. Let the cell store the initial number R 0 . Cyclically shifting the contents of the cell to the left by 1/4 of the length of the cell, we get a new number R 0*. Similarly, by cyclically shifting the contents of a cell R 0 to the right by 1/4 of the cell length, we get the second number R 0**. Sum of numbers R 0 * and R 0** gives a new random number R one . Further R 1 is entered into R 0 , and the whole sequence of operations is repeated (see Fig. 22.8).

Note that the number resulting from the summation R 0 * and R 0 ** , may not fit entirely in the cell R one . In this case, extra digits should be discarded from the received number. Let us explain this for Fig. 22.8, where all cells are represented by eight binary digits. Let R 0 * = 10010001 2 = 145 10 , R 0 ** = 10100001 2 = 161 10 , then R 0 * + R 0 ** = 100110010 2 = 306 10 . As you can see, the number 306 occupies 9 digits (in the binary number system), and the cell R 1 (as well as R 0 ) can hold a maximum of 8 bits. Therefore, before entering the value in R 1 it is necessary to remove one "extra", leftmost bit from the number 306, resulting in R 1 will go no longer 306, but 00110010 2 = 50 10 . Also note that in languages ​​such as Pascal, "truncation" of extra bits when a cell overflows is done automatically in accordance with the given variable type.

Linear congruent method

The linear congruential method is one of the simplest and currently most used procedures that simulate random numbers. This method uses the mod( x, y) , which returns the remainder after dividing the first argument by the second. Each subsequent random number is calculated based on the previous random number using the following formula:

r i+ 1 = mod( k · r i + b, M) .

The sequence of random numbers obtained using this formula is called linear congruent sequence. Many authors refer to a linear congruent sequence as b = 0 multiplicative congruent method, and when b ≠ 0 — mixed congruent method.

For a high-quality generator, it is required to select suitable coefficients. It is necessary that the number M was quite large since the period cannot have more M elements. On the other hand, the division used in this method is a rather slow operation, so for a binary computer, the logical choice would be M = 2 N, because in this case, finding the remainder of the division is reduced inside the computer to the binary logical operation "AND". It is also common to choose the largest prime number M, less than 2 N: in the special literature it is proved that in this case the least significant digits of the resulting random number r i+ 1 behave just as randomly as the older ones, which has a positive effect on the entire sequence of random numbers as a whole. An example is one of Mersenne numbers, equal to 2 31 1 , and thus, M= 2 31 1 .

One of the requirements for linear congruent sequences is the longest possible period. The length of the period depends on the values M , k and b. The theorem that we present below allows us to determine whether it is possible to achieve a period of maximum length for specific values M , k and b .

Theorem. Linear congruent sequence defined by numbers M , k , b and r 0 , has a period of length M if and only if:

• numbers b and M coprime;
• k 1 x p for every simple p, which is a divisor M ;
• k 1 is a multiple of 4 if M multiple of 4.

Finally, let's conclude with a couple of examples of using the linear congruential method to generate random numbers.

It was found that a series of pseudo-random numbers generated based on the data from example 1 will be repeated every M/4 numbers. Number q is set arbitrarily before the start of calculations, however, it should be borne in mind that the series gives the impression of being random at large k(and hence q). The result can be slightly improved if b odd and k= 1 + 4 q in this case, the series will be repeated every M numbers. After a long search k the researchers settled on the values ​​69069 and 71365.

The random number generator using the data from example 2 will generate random non-recurring numbers with a period of 7 million.

A multiplicative method for generating pseudo-random numbers was proposed by D. H. Lehmer in 1949.

Checking the quality of the generator

The quality of the entire system and the accuracy of the results depend on the quality of the RNG. Therefore, the random sequence generated by the RNG must satisfy a number of criteria.

The checks carried out are of two types:

• checks for uniform distribution;
• testing for statistical independence.

Checks for uniform distribution

1) RNG should give close to the following values ​​of statistical parameters characteristic of a uniform random law:

2) Frequency test

The frequency test allows you to find out how many numbers fell into the interval (m r – σ r ; m r + σ r) , i.e. (0.5 0.2887; 0.5 + 0.2887) or eventually (0.2113; 0.7887) . Since 0.7887 0.2113 = 0.5774 , we conclude that in a good RNG, about 57.7% of all random numbers drawn should fall into this interval (see Fig. 22.9).

It should also be taken into account that the number of numbers in the interval (0; 0.5) should be approximately equal to the number of numbers in the interval (0.5; 1) .

3) Chi-square test

The chi-square test (χ 2 -test) is one of the most famous statistical tests; it is the main method used in combination with other criteria. The chi-square test was proposed in 1900 by Karl Pearson. His remarkable work is regarded as the foundation of modern mathematical statistics.

For our case, a chi-square test will allow us to find out how much the created by us real The RNG is close to the RNG reference, i.e. whether it satisfies the uniform distribution requirement or not.

frequency diagram reference The RNG is shown in fig. 22.10. Since the distribution law of the reference RNG is uniform, the (theoretical) probability p i hitting numbers in i-th interval (total of these intervals k) is equal to p i = 1/k . And thus, in each k intervals will fall smooth on p i · N numbers ( N total number of generated numbers).

A real RNG will produce numbers distributed (and not necessarily evenly!) k intervals and each interval will include n i numbers (total n 1 + n 2 + + n k = N ). How can we determine how good and close the tested RNG is to the reference one? It is quite logical to consider the squares of the differences between the received number of numbers n i and "reference" p i · N . Let's add them up, and as a result we get:

χ 2 exp. =( n 1 p one · N) 2 + (n 2 p 2 · N) 2 + + ( n k – p k · N) 2 .

It follows from this formula that the smaller the difference in each of the terms (and hence the smaller the value of χ 2 exp. ), the stronger the law of distribution of random numbers generated by a real RNG tends to be uniform.

In the previous expression, each of the terms is assigned the same weight (equal to 1), which in fact may not be true; therefore, for the chi-square statistic, it is necessary to normalize each i th term, dividing it by p i · N :

Finally, let's write the resulting expression more compactly and simplify it:

We have obtained the value of the chi-square test for experimental data.

In table. 22.2 are given theoretical chi-squared values ​​(χ 2 theor.), where ν = N 1 is the number of degrees of freedom, p is a user-specified confidence level that specifies how much the RNG should meet the uniform distribution requirements, or p — is the probability that the experimental value χ 2 exp. will be less than the tabulated (theoretical) χ 2 theor. or equal to it.

Consider acceptable p from 10% to 90%.

If χ 2 exp. much more than χ 2 theor. (that is p is large), then the generator does not satisfy the requirement of a uniform distribution, since the observed values n i go too far from theoretical p i · N and cannot be regarded as random. In other words, such a large confidence interval is established that the restrictions on the numbers become very loose, the requirements on the numbers are weak. In this case, a very large absolute error will be observed.

Even D. Knuth in his book "The Art of Programming" noted that having χ 2 exp. small is also, in general, not good, although it seems, at first glance, remarkable from the point of view of uniformity. Indeed, take a series of numbers 0.1, 0.2, 0.3, 0.4, 0.5, 0.6, 0.7, 0.8, 0.9, 0.1, 0.2, 0.3, 0.4, 0.5, 0.6, they are ideal in terms of uniformity, and χ 2 exp. will be practically zero, but you are unlikely to recognize them as random.

If χ 2 exp. much less than χ 2 theor. (that is p small), then the generator does not satisfy requirement of a random uniform distribution, since the observed values n i too close to theoretical p i · N and cannot be regarded as random.

But if χ 2 exp. lies in a certain range, between two values ​​of χ 2 theor. , which correspond, for example, p= 25% and p= 50%, then we can assume that the values ​​of random numbers generated by the sensor are completely random.

In addition, it must be borne in mind that all values p i · N must be large enough, for example, greater than 5 (found empirically). Only then (with a sufficiently large statistical sample) can the experimental conditions be considered satisfactory.

So, the verification procedure is as follows.

Tests for statistical independence

1) Checking for the frequency of occurrence of a digit in a sequence

Consider an example. The random number 0.2463389991 consists of the digits 2463389991, and the number 0.5467766618 consists of the digits 5467766618. Combining the sequences of digits, we have: 24633899915467766618.

It is clear that the theoretical probability p i fallout i th digit (from 0 to 9) is 0.1.

2) Checking the appearance of series of identical numbers

Denote by n L number of series of identical consecutive digits of length L. Everything needs to be checked L from 1 to m, where m is a user-specified number: the maximum number of identical digits that occur in a series.

In the example "24633899915467766618", 2 series of length 2 (33 and 77) were found, that is n 2 = 2 and 2 series of length 3 (999 and 666), i.e. n 3 = 2 .

The probability of a series with a length of L is equal to: p L= 9 10 L (theoretical). That is, the probability of occurrence of a series with a length of one character is equal to: p 1 = 0.9 (theoretical). The probability of a two-character series appearing is: p 2 = 0.09 (theoretical). The probability of a three-character series appearing is: p 3 = 0.009 (theoretical).

For example, the probability of occurrence of a series with a length of one character is equal to p L= 0.9 , since there can be only one character out of 10, and only 9 characters (zero is not counted). And the probability that two identical characters "XX" will meet in a row is 0.1 0.1 9, that is, the probability of 0.1 that the character "X" will appear in the first position is multiplied by the probability of 0.1 that the same character will appear in the second position "X" and multiplied by the number of such combinations 9.

The frequency of occurrence of series is calculated according to the “chi-square” formula we have previously analyzed using the values p L .

Note: The generator can be checked multiple times, but checks are not complete and do not guarantee that the generator produces random numbers. For example, a generator that produces the sequence 12345678912345 will be considered ideal during checks, which, obviously, is not entirely true.

In conclusion, we note that the third chapter of the book "The Art of Programming" by Donald E. Knuth (volume 2) is completely devoted to the study of random numbers. It explores various methods for generating random numbers, statistical criteria for randomness, and the transformation of uniformly distributed random numbers into other types of random variables. More than two hundred pages have been devoted to the presentation of this material.

Randomizer an easy way to determine the winner online. You have a list of participants. Paste it into the input field one below the other. Make one click and our randomizer will randomly number all participants. This randomizer can be used in many areas:

• determination of the winners of the drawings
• choose a winner from the list
• placement of players
• Who will go first to the board 🙂

But our randomizer uses several algorithms at the same time. This guarantees complete randomness in the selection of the winner. The list is shuffled several times randomly. And you can be sure that the randomizer will make a real random choice. The simple randomizer interface compensates for the complexity of the algorithms. Use our randomizer to pick a really fair winner online!

Need determine the winner for a giveaway or online contest? There is nothing easier. Paste the list of participants into the text box on this page. Click the generate button below. As a result, a list of participants with a number will appear.
Each participant is given a number at random. Here the number of numbers corresponds to the number of participants in the draw. Record the process of choosing the winner on video. This way you will determine the winner of any online contest.

The winners of the competition are determined

An online random number generator or randomizer will determine the winner honestly. This is the easiest way to blindly select. You no longer need to pull a ticket from the lottery drum. It is enough to enter a list of applicants and choose the winner. By filming the random selection process on video, you will ensure a fair draw. In addition to this option for determining the winners, we still have. This draw will be useful for viewing the result of the draw at the same time. True, in this case, you will receive only the numbers of the winners of the competition. That is, numbers must be assigned to participants in advance.

Determine the winner with a random number generator (RNG)

The Random Number Generator (RNG) does an excellent job of determining the winner. You can't bribe him. The RNG is a blind choice because it doesn't care who wins the contest. Of course, you yourself can poke your finger at the list and determine the winner. But it will work for draws with prizes of little material value. There are much more serious draws. For example drawing a new iPhone. Then your audience will demand a transparent competition. This can only be done with . Or rather, a list randomizer. By filming such a process on video, you will demonstrate the randomness of choosing the winner. Such generators help not only in contests or sweepstakes. You can play the lottery with them. Find out the answer to a yes or no question. And also to solve any issues where you need to rely on chance.

Randomly determine the winner

We remind you again. This randomizer makes a random selection of the winner in any draws. It can be a draw in social networks VK, Facebook, Instagram. Or just a competition on the site. So that there are no doubts about the honesty of the competition among the participants, an RNG is needed for the competition. That's what our site is for. We generate random numbers, answers, tips. Randomly determine the winners of competitions and much more.

Other randomizers on the site

Other major randomizers include:

• nicknames
• cubes
• nicknames by name

Our generators use two methods to generate random numbers:

1. Based on the ping speed of third-party resources
2. Based on random seed with multiple level generation.

If you want to create a real random number sequence for your needs. For example, for a cipher. Which definitely cannot be reproduced. I suggest using ours. And all other randomizers will be useful for everyday tasks.

Holding various lotteries, drawings, etc. is often held in many groups or publics in, etc., and is used by account owners to attract a new audience to the community.

The result of such draws often depends on the luck of the user, since the recipient of the prize is determined randomly.

For such a determination, draw organizers almost always use an online random number generator or a pre-installed one that is distributed free of charge.

Choice

Quite often, it can be difficult to choose such a generator, since their functionality is quite different - for some it is significantly limited, for others it is quite wide.

A fairly large number of such services are being implemented, but the difficulty is that they differ in scope.

Many, for example, are tied with their functionality to a particular social network (for example, many generator applications only work with links of this one).

The simplest generators simply generate a random number within a given range.

This is convenient because it does not associate the result with a specific post, which means that they can be used for draws outside the social network and in various other situations.

They don't really have any other use.

Advice! When choosing the most suitable generator, it is important to consider the purpose for which it will be used.

Specifications

For the fastest process of choosing the optimal online random number generation service, the table below shows the main technical characteristics and functionality of such applications.

All applications discussed in the table are described in more detail below.

randstuff

You can use this application online using the link to its official website http://randstuff.ru/number/.

This is a simple random number generator, characterized by fast and stable operation.

It is successfully implemented both in the format of a separate independent application on the official website, and as an application in .

The peculiarity of this service is that it can choose a random number both from the specified range and from a specific list of numbers that can be specified on the site.

• Stable and fast work;
• Lack of direct link to the social network;
• You can select one or more numbers;
• You can only choose from the given numbers.

User reviews about this application are as follows: “We determine the winners in VKontakte groups through this service. Thank you”, “You are the best”, “I use only this service”.

Cast Lots

This application is a simple function generator, implemented on the official website, in the form of a VKontakte application.

There is also a generator widget to embed on your site.

The main difference from the previous described application is that this allows you to disable the repetition of the result.

That is, when conducting several generations in a row in one session, the number will not repeat.

• The presence of a widget to insert on a website or blog;
• Ability to disable the repetition of the result;
• The presence of the function "even more randomness", after the activation of which the selection algorithm changes.

User reviews are as follows: “It works stably, it is quite convenient to use”, “Convenient functionality”, “I use only this service”.

Random number

Various lotteries, drawings, etc. are often held in many groups or publics on social networks, Instagram, etc., and are used by account owners to attract a new audience to the community.

The result of such draws often depends on the luck of the user, since the recipient of the prize is determined randomly.

For such a determination, draw organizers almost always use an online random number generator or a pre-installed one that is distributed free of charge.

Choice

Quite often, it can be difficult to choose such a generator, since their functionality is quite different - for some it is significantly limited, for others it is quite wide.

A fairly large number of such services are being implemented, but the difficulty is that they differ in scope.

Many, for example, are tied with their functionality to a particular social network (for example, many generator applications on VKontakte work only with links of this social network).

The simplest generators simply generate a random number within a given range.

This is convenient because it does not associate the result with a specific post, which means that they can be used for draws outside the social network and in various other situations.

They don't really have any other use.

<Рис. 1 Генератор>

Advice! When choosing the most suitable generator, it is important to consider the purpose for which it will be used.

Specifications

For the fastest process of choosing the optimal online random number generation service, the table below shows the main technical characteristics and functionality of such applications.

All applications discussed in the table are described in more detail below.

<Рис. 2 Случайные числа>

randstuff

<Рис. 3 RandStuff>

You can use this application online using the link to its official website http://randstuff.ru/number/.

This is a simple random number generator, characterized by fast and stable operation.

It is successfully implemented both in the format of a separate independent application on the official website, and as an application in the social network VKontakte.

The peculiarity of this service is that it can choose a random number both from the specified range and from a specific list of numbers that can be specified on the site.

Pros:

• Stable and fast work;
• Lack of direct link to the social network;
• You can select one or more numbers;
• You can only choose from the given numbers.

Minuses:

• The impossibility of holding a draw on VKontakte (this requires a separate application);
• Applications for VKontakte do not run in all browsers;
• The result sometimes seems predictable, since only one calculation algorithm is used.

User reviews about this application are as follows: “We determine the winners in VKontakte groups through this service. Thank you”, “You are the best”, “I use only this service”.

Cast Lots

<Рис. 4 Cast Lots>

This application is a simple function generator, implemented on the official website, in the form of a VKontakte application.

There is also a generator widget to embed on your site.

The main difference from the previous described application is that this allows you to disable the repetition of the result.

That is, when conducting several generations in a row in one session, the number will not repeat.

• The presence of a widget to insert on a website or blog;
• Ability to disable the repetition of the result;
• The presence of the function "even more randomness", after the activation of which the selection algorithm changes.

Negative:

• The impossibility of determining several results at once;
• Inability to select from a specific list of numbers;
• To select the winner in public, you must use a separate VKontakte widget.

User reviews are as follows: “It works stably, it is quite convenient to use”, “Convenient functionality”, “I use only this service”.

Random number

<Рис. 5 Случайное число>

This service is located at http://random number.rf/.

A simple generator with minimum of functions and additional features.

Can randomly generate numbers within a given range (maximum from 1 to 99999).

The site does not have any graphic design, and therefore the page is easy to load.

The result can be copied or downloaded with the click of a button.

Negative:

• No widget for VKontakte;
• There is no possibility of holding draws;
• There is no way to insert the result into a blog or website.

Here is what users say about this service: “Good generator, but not enough functions”, “Very few features”, “Suitable for quickly generating a number without unnecessary settings.”

randommus

<Рис. 6 Рандомус>

You can use this random number generator at http://randomus.ru/.

Another simple one, but functional random number generator.

The service has sufficient functionality for determining random numbers, however, it is not suitable for holding draws and other more complex processes.

Negative:

• The impossibility of holding draws based on post reposts, etc.
• There is no application for VKontakte or a widget for the site;
• It is not possible to disable repeating results.

In this article, we will consider the features of the algorithm random number generator in excel, and with examples, let's look at how to use the RAND and RANDBETWEEN functions in Excel to generate random numbers, random numbers with a given number of decimal places, dates and times.

Random number generator using the RAND function

The RAND function is one of two functions specifically designed for generating random numbers in excel. This function returns a random decimal number (real number) between 0 and 1.

RAND() is a volatile function, which means that each time the worksheet is calculated, a new random number is generated. And this happens every time you do something on the worksheet, such as updating a formula (not necessarily a RAND formula, any other formula on the worksheet), editing a cell, or entering new data.

The RAND function is available in all versions: Excel 2016, Excel 2013, Excel 2010, Excel 2007, Excel 2003.

Since the Excel RAND function has no arguments, you simply type =RAND() in a cell and then copy the formula to as many cells as you want:

Now let's take it one step further and write some RAND formulas to generate random numbers according to certain conditions.

Random number generator from zero to a given upper range limit

To go from zero to any value of N, you run the RAND function multiple times with N:

For example, to generate a sequence of random numbers greater than or equal to 0 but less than 50, use the following formula:

RAND()*50

Note. The upper bound value is never included in the returned random sequence. For example, if you want to get random numbers from 0 to 10, including 10, the correct formula is =RAND()*11.

Range Random Number Generator

To create random number in range, i.e. random number between any two numbers you specify, use the following RAND formula:

RAND() * (B - A) + A

Where A is the lower bound value (lowest number) and B is the upper bound value (highest number).

For example, to make a random number generator from 10 to 50, you can use the following formula:

RAND()*(50-10)+10

Note. This random number generation formula will never return a number equal to the largest number in the specified range (value B).

Random Integer Number Generator in Excel

To make the Excel RAND function generate random integers, take one of the above formulas and wrap it in a INT function.

To from 0 to 50:

INTEGER(RAND()*50)

To generate random integers from 10 to 50:

INTEGER (RAND()*(50-10)+10)

Random Number Generator in Excel - Generate Random Integer Numbers

Random number generator in Excel in a range using the RANDBETWEEN function

RANDBETWEEN is another function in Excel to create random number generator.. It returns random integers in the specified range:

RANDBETWEEN (lower bound; upper bound)

Obviously the lower bound is the smallest number and the upper bound is the largest number in the range of random numbers you want to get.

Like RAND, RANDBETWEEN in Excel is a mutable function, and it also returns a new random integer each time your table is recalculated or changed.

For example, in order to make a random integer generator from 10 to 50 (including 10 and 50) use the following RANDBETWEEN formula:

RANDBETWEEN(10, 50)

Random number generator in Excel - Generate random numbers in a given range

The RANDBETWEEN function in Excel can generate both positive and negative random numbers. For example, to get a list of random numbers between -10 and 10, enter the following formula on a worksheet:

RANDBETWEEN(-10;10)

The RANDBETWEEN function is available in the following versions: Excel 2016, Excel 2013, Excel 2010 and Excel 2007.

In an earlier version Excel 2003, you can use the RAND formula discussed above.

Generating random numbers with a given number of decimal places

Although the RANDBETWEEN function in Excel was designed to generate random integers, you can use it to generate random decimal numbers with as many decimal places as you want.

RANDBETWEEN(lower bound*10; upper bound*10)/10

For example, to get a list of numbers with one decimal place, you multiply the bottom and top values ​​by 10, then divide the returned value by 10:

The following RANDBETWEEN formula returns random decimal numbers between 1 and 50:

RANDOMBETWEEN(1*10;50*10)/10

Random number generator in Excel - Generate random numbers with one decimal place

In a similar way to make a random number generator from 1 to 50 with two decimal places, you multiply the arguments of the RANDBETWEEN function by 100 and then divide the result by 100:

RANDBETWEEN(1*100; 50*100)/100

Random number generator in Excel - Generate random numbers with two decimal places

Random Date Generator in Excel

To return a list of random dates between the given two dates, use the RANDBETWEEN function in conjunction with DATE:

RANDOMBETWEEN(DATE(start date), DATE(end date))

For example, to get a list of dates between September 1, 2017 and November 20, 2017, inclusive, enter the following formula in a worksheet:

RANDOMBETWEEN(DATE(2017,9,1),DATE(2017,11,21))

Don't forget to apply the date format to the cell(s) and you will get a list of random dates like this:

Random Number Generator in Excel - Random Date Generation

Random Time Generator in Excel

In Excel's internal system, times are stored as decimal numbers, and you can use the standard Excel RAND function to insert random real numbers, and then simply apply the time format to the cells:

Random Number Generator in Excel - Generate random time with RAND function and apply Time format to it

To make a random time generator within the specified range requires a more specific formula. Let's consider in more detail.

Random time generator in the specified range

To insert an arbitrary time between any two time intervals you specify, use the TIME function in conjunction with Excel RAND:

TIME (start time) + RAND() * (TIME (start time) - TIME (end time))

For example, to insert a random time between 5:30 AM and 6:00 PM, you can use one of the following formulas:

TIME(5;30;0)+RAND()*(TIME(18;0;0)-TIME(5;0;0))

Random Number Generator in Excel - Generate random times in a given interval

Random Letter Generator in Excel

To insert a random letter, a combination of three different functions must be used:

CHAR(RANDOMBETWEEN(CODE("A");CODE("Z")))

Where A is the first character and Z is the last character in the range of letters you want to include (in alphabetical order).

Let's analyze the functions in the above formula:

• CODE returns the ANSI numeric codes for the specified letters.
• RANDBETWEEN accepts the numbers returned by CODE functions as the lower and upper range values.
• CHAR converts the random ANSI codes returned RANDBETWEEN into the corresponding letters.

Random number generator in Excel - Generate random letters

Because the ANSI codes are different for uppercase and lowercase letters, this formula is case sensitive.

If someone knows ANSI character codes by heart, there's nothing stopping you from passing the codes directly into the RANDBETWEEN function.

For example, to get arbitrary uppercase letters between A (ANSI 65 code) and Z (ANSI 90 code), you write:

CHAR(RANDOMBETWEEN (65,90))

To generate lowercase letters between a (ANSI 97 code) to z (ANSI 122 code), you use the following formula:

CHAR(RANDOMBETWEEN(97,122))

To insert a random special character like ! "#$%&"() *+, -./, use the RANDBETWEEN function with the bottom parameter set to 33 (ANSI code for "!") and the top parameter set to 47 (ANSI code for "/").

CHAR(RANDOMBETWEEN(33,47))

Random Number Generator in Excel - Random Character Generation

How to prevent repeated calculation of RAND and RANDBETWEEN

If you want to get a constant set of random numbers, dates, or text strings that won't change every time, i.e. fix the random numbers when the sheet is recalculated, use one of the following methods:

1. To stop the RAND or RANDBETWEEN functions from recalculating in a single cell, select that cell, switch to the formula bar, and press F9 to replace the formula with its value.
2. To prevent the random number function in Excel from automatically updating the values ​​in multiple cells, use the Paste function. Select all cells with random value generation formula, click ctrl+c to copy them, then right-click on the selected range and click " Paste special»--> «Values».

Random Number Generator in Excel - Inserting Values

Random Number Generator with Data Analytics

With a data analysis package, for example, you can or other distribution. By default, this package is not included, so you need to download it. How to do this is described in this.

An example of generating random numbers of a normal distribution

In order to generate random numbers of normal distribution, go to the tab " DATA", In the group" Analysis"Select" Data analysis".

Random Number Generator in Excel - Data Analysis

In the list that opens, select " Random number generation"and press the button" OK".

Random Number Generator in Excel - Random Number Generation

In the window that opens, in the list " Distribution» choose « Normal ”, enter the number of variables, the number of random numbers, the mean and deviation, and the place where you want to place the generated random numbers.

Random Number Generator in Excel - Normal Distribution Random Number Generation

After all the data is entered, press the button "OK", and as a result we get the generated random numbers of the normal distribution.

Well, that's all. Now you've learned how to make a random number generator, numbers in a range, numbers with a given number of decimal places, random dates, random times, and random letters, as well as how generate random numbers of normal distribution. Thus, having this knowledge, you can create not only random number generator in excel, but also .