Random number generator in Excel in a range

From time to time, Excel users need to generate random numbers in order to use them in formulas or for other purposes. To do this, the program provides a whole arsenal of possibilities. It is possible to generate random numbers in a variety of ways. We will cite only those that have shown themselves in practice in the best way.

Random Number Function in Excel

Suppose we have a dataset that must contain elements that are absolutely unrelated to each other. Ideally, they should be formed according to the law of normal distribution. To do this, you need to use the random number function. There are two functions with which you can achieve your goal: CALCULATION и BETWEEN THE CASE. Let’s take a closer look at how they can be used in practice.

Selecting random numbers with RAND

This function does not provide any arguments. But despite this, it allows you to customize the range of values ​​within which it should generate a random number. For example, to get it within the framework of one to five, we need to use the following formula: =COUNT()*(5-1)+1.

If this function is distributed to other cells using the autocomplete marker, then we will see that the distribution is even.

During each calculation of a random value, if you change any cell anywhere in the sheet, the numbers will be automatically generated again. Therefore, this information will not be stored. To make sure that they remain, you must manually write this value in numerical format, or use this instruction.

1. We make a click on a cell containing a random number.
2. We make a click on the formula bar, and then select it.
3. Press the F9 button on the keyboard.
4. We end this sequence of actions by pressing the Enter key.

Let’s check how uniformly the random numbers are distributed. To do this, we need to use the distribution histogram. To do it, follow these steps:

1. Let’s create a column with pockets, that is, those cells in which we will keep our ranges. The first one is 0-0,1. We form the following using this formula: =C2+$C$2. 
2. After that, we need to determine how often the random numbers associated with each specific range occur. For this we can use the array formula {=FREQUENCY(A2:A201;C2:C11)}. 
3. Next, using the “clutch” sign, we make our next ranges. The formula is simple =»[0,0-«&C2&»]». 
4. Now we are making a chart describing how these 200 values ​​are distributed. 

In our example, the frequency corresponds to the Y axis, and the “pockets” correspond to the X axis.

BETWEEN function

Speaking of function BETWEEN THE CASE, then according to its syntax, it has two arguments: a lower bound and an upper bound. It is important that the value of the first parameter is less than the second. It is assumed that the boundaries can be integers, and fractional formulas are not taken into account. Let’s see how this feature works in this screenshot.

We see that the accuracy can be adjusted using division. You can get random numbers with any digits after the decimal point.

We see that this function is much more organic and understandable for an ordinary person than the previous one. Therefore, in most cases, you can use only it.

How to make a random number generator in Excel

And now let’s make a small number generator that will receive values ​​based on a certain range of data. To do this, apply the formula =INDEX(A1:A10,INTEGER(RAND()*10)+1).  

Let’s create a random number generator that will be generated from zero to 10. Using this formula, we can control the step with which they will be generated. For example, you can create a generator that will only produce zero-terminated values. 

Or such an option. Let’s say we want to select two random values ​​from a list of text cells. 

And to choose two random numbers, you need to apply the function INDEX. 

The formula with which we did this is shown in the screenshot above. =ИНДЕКС(A1:A7;СЛУЧМЕЖДУ(1;СЧЁТЗ(A1:A7))) – with this formula, we can create a generator for a single text value. We see that we have hidden the auxiliary column. So can you. 

Normal Distribution Random Number Generator

Feature problem SLCHIS и BETWEEN THE CASE in that they form a set of numbers that are very far from the target. The probability that a number will appear close to the lower limit, middle or upper limit is the same.

A normal distribution in statistics is a set of data in which, as the distance from the center on the graph increases, the frequency with which a value occurs in a certain corridor decreases. That is, most of the values ​​​​accumulate around the central one. Let’s use the function BETWEEN THE CASE Let’s try to create a set of numbers, the distribution of which belongs to the category of normal.

So, we have a product, the production of which costs 100 rubles. Therefore, the numbers should be generated approximately the same. In this case, the average value should be 100 rubles. Let’s create an array of data and create a graph in which the standard deviation is 1,5 rubles, and the distribution of values ​​​​is normal.

To do this, you need to use the function =NORMONUM(SLNUMBER();100;1,5). Further, the program automatically changes the probabilities, based on the fact that numbers close to a hundred have the highest chance.

Now we just need to build a graph in the standard way, choosing a set of generated values ​​as a range. As a result, we see that the distribution is indeed normal.

It’s that simple. Good luck.