Excel tutorials

Excel functions RAND i RANDBETWEEN

In this Excel tutorial I will show a collection of Excel formulas that use RAND and RANDBETWEEN functions. If you need to display random numbers in a range or random values then study this tutorial and a collection of Excel formulas shown below.

Excel RAND function

The syntax of this function looks like this
=RAND()

After entering this formula, Excel will return a random number or a random value of six decimal places. If you want to use the RAND() function for a range of numbers, then you can use this formula (X is the smallest number and Y is the largest number). Instead of this second formula, you can simply use the RANDBETWEEN function.
=RAND()*(y-x)+x

Excel RANDBETWEEN function

The syntax of this function looks like this
=RANDBETWEEN(bottom;top)

If you want to display random values, between a number range, then use this Excel function. As you can conclude the 'bottom' argument is the smallest number and the 'top' argument is the largest number.

NOTE: By pressing the 'F9' key you automatically refresh the new random values (numbers).

RAND function and generating random numbers from 1 to 20

In the image below, you can see results based on the Excel RAND() function. There are still other nested functions, but the base is a RAND function. Using the RAND function, we can generate random numbers (random values) as in the 'A' column in Image 1.

Image 1.

Examples of RAND functions in Excel


How to use the RAND function in Excel

Formulas on Image 1 are as follows:

The formula in 'A2' is as follows: (This formula generate random values in the 'A' column.
=RAND()

The formula in 'B2' is as follows: (This formula ranges values from the 'A' column.
So the result of this formula for a particular value is the order of the value in the numerical sequence. Of course, there are no duplicates here since all results are unique values)
=RANK(A2;A$2:A$21)

Formulas in 'D', 'E' and 'F' columns are linked in a single unit. In the 'D2' cell number 20 is entered. This number indicates that we want twenty numbers (twenty values) from the 'A' column. These results are similar to those in the 'B' column, they are just set in two columns of ten numbers.

The formula in 'E2' cell is the following: (Copy this formula to the 'F' column and down to the bottom. (This formula has more nested Excel functions like ROW, RANK, OFFSET, LARGE, MATCH, COLUMN, INDEX)
=IF(ROW()-ROW(E$2)+1>$D$2/2;"";RANK(OFFSET($A$2;ROW()-ROW(E$2)+(COLUMN()-COLUMN($E2))*($D$2/2););$A$2:INDEX($A$2:$A$21;$D$2)))

The formula in the 'H' column is as follows: (This formula replaces the Excel RANDBETWEEN function. X and Y are the extreme values between which we seek random generated values. If you look at the formula below, then you can see that I set the end value, number 1 as the smallest and the number 20 as the highest.)
=RAND()*(20-1)+1

The formula in the 'I' column is as follows: (This formula is standard for ranking values in a given cell range)
=RANK(H2;H$2:H$21)

RANDBETWEEN function and generating random values in decimals

Excel RANDBETWEEN function, generates a sequence of values between the two default conditions. These conditions are the lowest and the highest value. At Image 2, note the three columns that show examples of using the RANDBETWEEN function.

Image 2.

Examples of RANDBETWEEN functions in Excel


How to use the RANDBETWEEN function to get results with two or more decimals

The formula in the 'A2' cell in the figure above is as follows: (Note that I have set the number 1 as the smallest value and the number 10 as the highest value. The result of this formula below is the number without decimal values)
=RANDBETWEEN(1;10)

The formula in the 'B2' cell in the figure above is as follows: (The result of this formula is the values rounded to two decimal places. Note that the lowest value is 20 and the highest value is number 30).
=RANDBETWEEN(20;30)/100

The formula in the 'C2' cell in the figure above is as follows: (The result of this formula is negative values rounded to three decimal places. Note that the lowest value is -100 and the highest value is -90).
=RANDBETWEEN(-100;-90)/1000

Generating random letters from 'A' to 'Z'

At Image 3 below, see how we can generate random group of character tags (randomly generate letter character groups) if the first letter is 'a' and the last little letter 'z'.

Image 3.

Generating random groups of six letters


Generate random group letters between two specific letters

The formula in the 'E2' cell is as follows: (Character, the small letter 'a' can be represented using the textual CODE function, ie the Excel formula below. Notice that the return formula is the result of the numerical code 97 for the small letter 'a').
=CODE(D2)

The formula in the 'B2' cell is as follows: (The base of this formula is CHAR and RANDBETWEEN functions. The CHAR function returns the letter character for a given number as a condition, so the formula =CHAR(97) returns a small letter 'a'. in the CHAR formula, the RANDBETWEEN function is nested).
=CHAR(RANDBETWEEN(97;122))&CHAR(RANDBETWEEN(97;122))&CHAR(RANDBETWEEN(97;122))&CHAR(RANDBETWEEN(97;122))

See the cell range 'G2: L11' in Image 3 above. Note that there are six characters in each cell of this range. This series of random groups of letters in the range was obtained by using this formula below: (Copy the Formula to the right and then copy it all down).
=CHAR(RANDBETWEEN(97;122))&CHAR(RANDBETWEEN(97;122))&CHAR(RANDBETWEEN(97;122))&CHAR(RANDBETWEEN(97;122))&CHAR(RANDBETWEEN(97;122))&CHAR(RANDBETWEEN(97;122))

Generating random number lists from cell ranges

If you have a list of values that are in the range and want to select random values from that list then you can again use the RANDBETWEEN function nested in the INDEX formula. Note that the same formula is used for both argument (row_num, column_num) of the INDEX function. The only difference is the value of another argument within the RANDBETWEEN formula. Since the argument 'row_num' INDEX function requires the number of order and we have ten rows in the cell range, so I set the highest value 10. The same applies to the number of columns. I hope you understand that I do not write much now. In fact, consider the more detailed INDEX function, its syntax and arguments. See Image 4 below.
=INDEX($A$1:$E$10;RANDBETWEEN(1;10);RANDBETWEEN(1;5))

Image 4.

Generating random number lists from cell range

How to display or generate a random name from the name list

If you want to select a random name or text from a cell range, then use the formulas below Image 5. Note in the figure below that the names of the persons are in the 'A2:A8' range. As a result, our goal is to restore a random name (something similar to generating Lottery numbers :-)

Image 5.

How to display or select a random name from the name list

In the picture above, notice two formulas that return a randomly selected name from the name list.

The formula in 'C2' cell is as follows: (Note that the RANDBETWEEN function is nested in the CHOOSE function).
=CHOOSE(RANDBETWEEN(1;7);A2;A3;A4;A5;A6;A7;A8)

The formula in the 'C4' cell is the following: (In this formula only the first two arguments of the INDEX function were used, so the 'array' and 'row_num' arguments are used.
=INDEX($A$2:$A$8;RANDBETWEEN(1;7))

Generate random numbers for the Lotto - Generator Lottery numbers

This example on Image6 is based to the Lotto game of luck. In the 'A' column all numbers from 1 to 39 are generated. The formula is below. If you are wondering how to say now that I have specified the smallest and largest number I used the RAND function. So just look at the row numbers in the 'A' column.
=RAND()

Image 6.

Generating random Lottery numbers


Generator of Lotto Numbers

In the second row Image6, from the 'C2' cell to the 'G2' cell, the formula below was used. (this formula is based on 'A' column and returns a random number from the range.) I suppose you know that the MATCH function returns the number of rows as a result, ie returns a value from a certain order in this case).
=MATCH(LARGE($A$1:$A$39;COLUMN(A1));$A$1:$A$39;0)

Extra Lotto number in 'H2' cell is obtained by the standard formula below.
=RANDBETWEEN(1;39)

If you do not want to use the above variant, you can use two conditions for minimum and maximum values (the smallest number and maximum number) in the 'G8' and 'G9' cells.

CSE Formula in 'C9' cell is the following: (This ARRAY formula returns Unique numbers between two conditions. Copy this formula down to the desired row).
=IFERROR(LARGE(ROW(INDIRECT($G$8&":"&$G$9))*NOT(COUNTIF($C$8:C8;ROW(INDIRECT($G$8&":"&$G$9))));RANDBETWEEN(1;$G$9-$G$8-ROW(A1)+2));"")

Automatically generate a list of names

If you want to automatically generate a random order of the text (in this case the random order of the person's name, then look at Image7 below.) In this case, auxiliary columns or "C" and "E" columns are used, which of course you can visually hide. in the 'A' column. In the 'F' column using the formula, a list of names has been generated in random order.

Image 7.

List of names with random order


Generate a list of random order names from the list

The formula in 'C' column is as follows: (Again we used the standard RAND formula that has returned nine random numbers).
=RAND()

In the 'E' column, an auxiliary column has the formula below: (This formula values the values from the 'C' column.
=RANK(C2;$C$2:$C$10)

Finally, showing the random order of a person's name in the 'F' column is given below.
=INDEX($A$2:$A$10;MATCH(ROW(A1);$E$2:$E$10;0))

I hope that with this tutorial I managed to get you closer to doing two Excel functions RAND and RANDBETWEEN.

Press the 'F9' key to refresh the formula calculation. Remember, after pressing the 'F9' key, all the values (all numbers) that have been generated with Excel functions RAND and RANDBETWEEN are automatically updated (automatically changed on each worksheet containing the respective Excel function).

Finally, in the attachment of this tutorial, you have the Download file option on which I did this tutorial.

Pay attention, you may need as a delimiter of arguments formula use a comma (,) instead of a semicolon (;).
Copy formula to Notepad and with Find/Replace change semicolon to comma.