We have a sequence of numbers consisting of practically independent elements that obey a 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 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.
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 bound). 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.
You need to select 2 random ones from the list of text values. 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 select one random value from the list, use 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 a 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 build a graph with a normal distribution with a standard deviation of 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.
Please suspend AdBlock on this site.
Sometimes it may be necessary to generate random numbers. A simple example.
Example: Determining the winner in a repost competition.
There is a list of 53 people. It is necessary to choose a winner from them. If you choose it yourself, you may be accused of bias. So you decide to write a program. It will work as follows. You enter the number of participants N, after which the program displays one number - the winner’s number.
You already know how to get a number from a player. But how can you force a computer to think of a random number? In this lesson you will learn how to do this.
rand() function.
This function returns a random integer in the range from zero to RAND_MAX. RAND_MAX is a special C constant that holds the maximum integer value that can be returned by the rand() function.
The rand() function is defined in the stdlib.h header file. Therefore, if you want to use rand in your program, do not forget to include this header file. The RAND_MAX constant is also defined in this file. You can find this file on your computer and see its meaning.
Let's see this feature in action. Let's run the following code:
Listing 1.
#include
It should look something like this.
Fig.1 Five random numbers generated by the rand function
But we would like to get numbers from 1 to 53, and not everything in a row. Here are a few tricks to help you constrain the rand() function.
Limit random numbers from above.
Anyone who waited at school for the moment when mathematics would come in handy, get ready. The moment has arrived. To limit random numbers from above, you can use the operation of obtaining the remainder of division, which you learned in the last lesson. You probably know that the remainder of division by numbers K is always less than the number K. For example, dividing by 4 can result in remainders of 0, 1, 2, and 3. Therefore, if you want to limit random numbers from above to the number K, then simply take the remainder of division by K. Like this:
Listing 2.
#include
Fig.2 Five random numbers less than 100
Limit the numbers below.
The rand function returns random numbers from the interval. What if we only need numbers larger than M (for example, 1000)? What should I do? It's simple. Let's just add our value M to what the rand function returned. Then if the function returns 0, the final answer will be M, if 2394, then the final answer will be M + 2394. With this action we seem to shift all numbers forward by M units.
Set the rand function's upper and lower bounds.
For example, get numbers from 80 to 100. It seems like you just need to combine the two methods above. We'll get something like this:
Listing 3.
#include
Try running this program. Surprised?
Yes, this method will not work. Let's run this program by hand to see if we made a mistake. Let's say rand() returned the number 143. The remainder when divided by 100 is 43. Then 80 + 43 = 123. So this method doesn't work. This design will produce numbers from 80 to 179.
Let's break down our expression step by step. rand()%100 can return numbers from 0 to 99 inclusive. Those. from the segment.
Operation + 80 shifts our segment 80 units to the right. We get.
As you can see, our problem lies in the right border of the segment; it is shifted to the right by 79 units. This is our original number 80 minus 1. Let's clean things up and move the right border back: 80 + rand()%(100 - 80 + 1) . Then everything should work as it should.
In general, if we need to get numbers from the segment, then we need to use the following construction:
A + rand()%(B-A+1) .
According to this formula, we will rewrite our last program:
Listing 4.
#include
Result:
Fig.3 Random numbers from a range
Well, now you can solve the original problem of the lesson. Generate a number from a segment. Or can't you?
But first, some more useful information. Run the last program three times in a row and write down the random numbers it generates. Did you notice?
srand() function.
Yes, the same identical numbers appear every time. “So-so generator!” - you say. And you will not be entirely right. Indeed, the same numbers are generated all the time. But we can influence this by using the srand() function, which is also defined in the stdlib.h header file. It initializes the random number generator with a seed number.
Compile and run this program several times:
Listing 5.
#include
Now change the argument of the srand() function to another number (I hope you haven’t forgotten what a function argument is?) and compile and run the program again. The sequence of numbers must change. As soon as we change the argument in the srand function, the sequence also changes. Not very practical, is it? To change the sequence, you need to recompile the program. If only this number would be inserted there automatically.
And it can be done. For example, let's use the time() function, which is defined in the time.h header file. This function, if NULL is passed as an argument, returns the number of seconds that have passed since January 1, 1970. Here's a look at how it's done.
Listing 6.
#include
You may ask, what is NULL? A reasonable question. In the meantime, I’ll tell you what this special reserved word is. I can also say what a null pointer means, but... This doesn’t provide you with any information, so I recommend not thinking about it at the moment. And just remember it as some kind of clever trick. In future lessons we will look at this thing in more detail.
In educational algorithmic problems, the need to generate random integers is quite common. Of course, you can receive them from the user, but problems may arise with filling the array with 100 random numbers.
The standard library function of the C language (not C++) rand() comes to our aid.
int rand(void);
It generates a pseudo-random integer in the range of values from 0 to RAND_MAX. The latter is a constant that varies depending on the language implementation, but in most cases it is 32767.
What if we need random numbers from 0 to 9? A typical way out of this situation is to use the modulo division operation.
If we need numbers from 1 (not 0) to 9, then we can add one...
The idea is this: we generate a random number from 0 to 8, and after adding 1 it turns into a random number from 1 to 9.
And lastly, the saddest thing.
Unfortunately, the rand() function generates pseudo-random numbers, i.e. numbers that seem random, but are actually a sequence of values calculated using a clever algorithm that takes the so-called grain as a parameter. Those. The numbers generated by the rand() function will depend on the value that the grain has at the time it is called. And the grain is always set by the compiler to the value 1. In other words, the sequence of numbers will be pseudo-random, but always the same.
And this is not what we need.
The srand() function helps correct the situation.
void srand(unsigned int seed);
It sets the grain equal to the value of the parameter with which it was called. And the sequence of numbers will also be different.
But the problem remains. How to make the grain random, because everything depends on it?
A typical way out of this situation is to use the time() function.
time_t time(time_t* timer);
It is also inherited from the C language and, when called with a null pointer as a parameter, returns the number of seconds that have passed since January 1, 1970. No, this is not a joke.
Now we can pass the value of this function into the srand() function (which does an implicit cast), and we will have a wonderful random grain.
And the numbers will be wonderful and non-repetitive.
To use the rand() and srand() functions you need to include a header file
Here is a complete example.
#include
#include
#include
using namespace std;
int main()
{
cout<< "10 random numbers (1..100): " << endl;
srand(time(NULL));
for(int i=0;i<10;i++) cout << rand() % 100 + 1 << " ";
cin.get();
return 0;
}
The function that generates pseudorandom numbers has a prototype in the stdlib.h library file:
1
2
3
4
5
6
unsigned long int next = 1;
int rand(void)
{
next = next * 1103515245;
return ((unsigned int )(next / 65536) * 2768);
}
The rand() function takes no arguments, but operates on the next variable with global scope.
If you need to generate a sequence in the range , then the formula is used:
Number = rand()%(M2-M1+1) + M1;
Where Number– generated number. M2-M1+1– full range of number representation. M1– offset of the specified range relative to 0; % - remainder of the division .
For example, if you need to generate a sequence in the range [-10;10], then the function call will look like
Number = rand()%(10+10+1)-10
Number = rand()%(21)-10
As a result of obtaining the remainder from division by 21, we have a number from 0 to 20. Subtracting 10 from the resulting number, we obtain a number in the desired range [-10;10].
However, the sequence generated by the rand() function will look the same every time the program is run.
To generate different sequences each time the program is launched, it is necessary to initialize the global variable next with a value other than 1. For this purpose, use the function
void srand(unsigned int seed)
( next = seed; )
To ensure that the initialization of next is different each time the program is launched, the current time is most often used as the seed argument.
Example Fill an array of 20 elements with random numbers in the range from 0 to 99.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
#include
#include
#include
#define SIZE 20
int main() (
int a;
srand(time(NULL ));
for (int i = 0; i
a[i] = rand() % 100;
printf("%d " , a[i]);
}
getchar();
return 0;
}
Execution result
Often the task arises of arranging an existing set of values in random order. A pseudorandom number generator is also used for this purpose. This creates an array and fills it with values.
The mixing procedure itself is as follows. Two array index values are generated randomly, and the values of the elements with the resulting indices are swapped. The procedure is repeated at least N times, where N is the number of array elements.
As an example, consider shuffling 20 values (from 1 to 20) and repeating the procedure 20 times.
Implementation in C
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
#include
#include
#include
#define SIZE 20
int main() (
int a;
srand(time(NULL ));
for (int i = 0; i< SIZE; i++)
{
a[i] = i + 1;
printf("%2d " , a[i]);
}
for (int i = 0; i< SIZE; i++)
{
// Generate randomly two indexes of elements
int ind1 = rand() % 20;
int ind2 = rand() % 20;
// and swap elements with these indices
int temp = a;
a = a;
a = temp;
}
printf("\n" );
for (int i = 0; i< SIZE; i++)
printf("%2d " , a[i]);
getchar();
return 0;
}
Execution result
Often the task of randomly selecting previously specified array elements arises. Moreover, it is necessary to ensure the absence of repetition in the selection of these elements.
The algorithm for this choice is as follows:
- We randomly select the index of an array element
- If an element with the same index has already been selected, move to the right until we reach the next unselected element. At the same time, we make sure that the “movement to the right” does not go beyond the boundaries of the array. If an out of bounds of the array is detected, we begin viewing the array elements from the beginning.
- Selecting an element
- Fixing the element as selected
- Repeat these steps for all other elements
Implementations in C
As a result, we obtain a new array b, formed by a random selection of elements of the array a.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
#include
#include
#include
#define SIZE 20
int main() (
int a;
int b; // resulting array
srand(time(NULL ));
// Fill the array with consecutive values from 1 to 20
for (int i = 0; i< SIZE; i++)
{
a[i] = i + 1;
printf("%2d " , a[i]);
}
for (int i = 0; i< SIZE; i++)
{
int ind = rand() % 20; // select an arbitrary index
while (a == -1) // while the element is "selected"
{
ind++; // move to the right
ind %= 20; // if we reach the right border, return to the beginning
}
b[i] = a; // write the next element of the array b
a = -1; // mark array element a as "selected"
}
printf("\n" );
// Output the resulting array
for (int i = 0; i< SIZE; i++)
printf("%2d " , b[i]);
getchar();
return 0;
}
Execution result
Tags: C random, C random numbers, random number generation, RNG, pseudo-random numbers, Monte Carlo method
Pseudorandom numbers
Generating pseudorandom numbers is a complex mathematical problem. This article does not set out to cover this topic. In what follows, the concept of “random number” will mean pseudo-random, unless otherwise specified.
You come across examples of the use of random numbers everywhere. Pseudorandom numbers are used in design and graphics, to generate levels in computer games and to simulate AI. Sets of random numbers are used in mathematical algorithms (see Monte Carlo methods).
Obviously, the problem of generating random numbers cannot be solved on a classical processor, since the operation of a computer is deterministic by definition. However, it is possible to generate very long sets of numbers such that their distribution has the same properties as sets of truly random numbers.
It is important that to solve a particular problem you need to choose the right generator, or at least know its properties. For example, when modeling a physical process, you can get completely different and often incorrect results, depending on the choice of random number generator.
Let's look at a standard generator.
#include
First, you need to initialize the random number generator (RNG, or RNG - random number generator), set the seed, on the basis of which the generation will take place in the future. It is important that for the same initial value the generator will return the same numbers.
Srand(42);
Assign variable r a random value
R = rand();
The value will be in the range from 0 to RAND_MAX.
In order to get a new set of numbers the next time you start, you need to initialize the generator with different values each time. For example, you can use the system time:
Srand(time(NULL));
Srand(_getpid());
The getpid function of the process.h library returns the process ID (you can also use getpid, the non-POSIX version of the function).
Central Limit Theorem
It is very important to immediately remind or introduce the central limit theorem. Informal definition: the distribution of a sum of weakly dependent random variables tends to normal. Finger-shaped explanation: if you add several random variables, regardless of their distribution, the distribution of the sum will be normal. You can often see code like this
#include
Generating random numbers on a given interval
First, we get a random number from zero to one:
Const float RAND_MAX_F = RAND_MAX; float get_rand() ( return rand() / RAND_MAX_F; )
To get a number in the interval from zero to N, multiply N by a random number from zero to one. To obtain a random number from M to N, we shift the resulting number by M.
Float get_rand_range(const float min, const float max) ( return get_rand() * (max - min) + min; )
To obtain an integer, we will take the remainder of division by the length of the interval. But the remainder of the division will return a number one less than our interval, so we increase it by one:
Int get_rand_range_int(const int min, const int max) ( return rand() % (max - min + 1) + min; )
An example of using random numbers to calculate an integral. Let us have some smooth function of one variable. Let's limit it to a square from a to b, and from 0 to some point, which is obviously larger than our function.
We will randomly throw points on our square. If they lie above the function (shown as green crosses in the figure), then we will assign them to the first group A, if below the function (red in the figure), then we will assign them to the second group B. The position of the points is random and distributed evenly (since the standard the generator gives a uniform distribution. This simple example, by the way, already shows how important it is to know the properties of the RNG). Then the ratio of red dots to the total number of dots will be equal to the ratio of the area under the graph to the total area. And the total area is the square (b-a) by q.
Src="/images/c_random_integral.png" alt=" Everything that randomly falls above our function is green, everything below is red.
The ratio of green to red will be equal to the ratio of the area above the graph to the area below the graph."> Всё, что случайно попадает выше нашей функции - зелёное, всё что ниже - красное. !}
The ratio of green to red will be equal to the ratio of the area above the graph to the area below the graph.
Let's apply our calculations - find the integral of the function x^2 on the interval from 0 to two in two ways.
#include
square = (b - a) * q * ratio;
return square; ) int main() ( float abs_ans = 2.66667f; float sr = rand_square(0, 2, 4); float ss = square_square(0, 2, 4); printf("Rounds = %d\n", ROUNDS); printf("Sa = %.5f\n", abs_ans); printf("Sr = %.5f\n", sr); printf("Ss = %.5f\n", ss); %.5f\n", fabs(sr - abs_ans)); printf("ds = %.5f\n", fabs(ss - abs_ans)); _getch(); return 0; )
Play with the ROUNDS value, change it and see how the calculation accuracy changes.
