Excel Randbetween - Free Excel Tutorial

April 8, 2023April 8, 2023

Excel Array Construction

This article will talk about how to create one-dimensional array or two-dimensional array by using some functions in Excel. When using array formulas in Excel, we often use functions to construct arrays.

1. Generate Array with ROW or COLUMN Functions

Array formulas often need to use “natural number” as parameters of the function, such as the second parameter of the LARGE function, OFFSET function in addition to the first parameter. Through the manual way to enter a constant array will be more trouble, and easy to make mistakes. Then we can use the ROW or COLUMN function in EXCEL to generate a sequence, this method is very convenient and fast.

The following formula produces a vertical array of natural numbers from 1 to 10.

{=ROW (1:10)}

The following formula produces a horizontal array of natural numbers from 1 to 10.

{=COLUMN(A:J)}

2. Generating two-dimensional Array from one-dimensional Array

Below we will show you how to construct a new two-dimensional array with two columns of data.

a. One-dimensional range rearrangement to generate two-dimensional array

If there is a list of students and the name column of that list contains 10 students’ names, we need to randomly place the students’ names in the name column into the cell range of 5 rows and 2 columns (a new two-dimensional array).

We can use the following steps to randomize the names of students in column B to a 5 row 2 column range, such as the cell range E1:F5.

STEP1# Select the cell range E1:F5

STEP2# Enter the following array formula in the formula bar

=INDEX(B2:B11,RIGHT(SMALL(RANDBETWEEN(A2:A11^0,999)/1%+A2:A11,ROW(1:5)*2-{1,0}),2))

STEP3# Press CTRL + SHIFT +ENTER shortcut keys to convert the above formula into an array formula.

STEP4# You will see that the students’ names have been randomly placed in a two-dimensional range of array.

Let’s see how this array formula works.

=RANDBETWEEN(A2:A11^0,999)

The RANDBETWEEN function is used to generate an array of 10 values, where the elements are random integers between 1 and 1000. Since the elements are randomly generated, the size of the array elements is randomly ordered. The array formula generates an array of random integers as follows.

={484;203;468;525;702;220;13;163;386;54}

=RANDBETWEEN(A2:A11^0,999)/1%+A2:A11

The random integer array generated above is multiplied by 100 and then added to the ordinal array of 1 to 10. This ensures that the last two digits of the array elements are ordinal numbers 1 to 10.

={484;203;468;525;702;220;13;163;386;54}/1%+A2:A11

The above array formula returns the following array:

{48401;20302;46803;52504;70205;22006;1307;16308;38609;5410}

=ROW(1:5)*2-{1,0}

The ROW function generates a vertical array {1;2;3;4;5), and then subtracts a constant array {1,0} to produce a two-dimensional array of 5 rows and 2 columns.

=SMALL()

The result is taken as the second argument to the SMALL function, which sorts the array after multiplication and addition processing. Since the original size of the array is random, after sorting, the ordinal number corresponding to the last two digits of each element is randomly ordered.

=RIGHT()

The RIGHT function is used to extract the last two digits of each element, and the INDEX function is used to return the student’s name in the corresponding position in column B. In this way, the names in column B can be randomly populated into a two-dimensional array of 5 rows and 2 columns.

b. Combining two columns of data to create a two-dimensional array

We can use the VLOOKUP function to query from the right to the left, and we can use the array operation and IF function to swap two columns of data to generate a new two-dimensional array.

Here is an example of how the VLOOKUP function can be used to reverse the query by constructing a new array.

If you have a table of employee information, and you need to find the employee’s number by the employee’s name, you can use the VLOOKUP function in combination with the IF function to construct a two-dimensional array to find the corresponding employee number.

The employee information table is as follows:

To find the employee number by name, the steps are as follows.

STEP1# Select the cell B3

STEP2# Enter the following formula in the formula bar and press Enter

=VLOOKUP(B2,IF({1,0},E2:E11,D2:D11),2,)

STEP3# As you can see, Jerry’s employee number has been found.

Let’s see how the above formula works.

The core part of the formula is IF ({1,0},E2:E11,D2:D11), which uses a horizontal array {1,0} and two vertical arrays to perform operations to achieve the position of the column where the employee’s name and work number are swapped. Its returned memory arrays are：

{"Tom",1;"Jerry",2;"Jacey",3;"Wendy",4;"William",5;"Ocean",6;"scott",7;"charies",8;"ceila",9;"nicole",10}

The VLOOKUP function then queries the employee’s name in the two-dimensional array generated by the IF function and returns the corresponding employee number.

3. Extracting Sub-arrays From Data

In daily work, it is often necessary to extract part of the data from a column and reprocess it. For example, If you want to find out the list of employees who meet the specified requirements in the employee table.

The following describes how to extract some data from a column to form a subarray.

Suppose you have an employee salary table and you want to find out the names of employees whose salary is greater than $2000. The salary table is as follows.

You can refer to the following steps to obtain a list of employees who meet the requirements.

STEP1# First you need to select the cell range E2:E11

STEP2# Enter the following formula in the formula bar

=T(OFFSET(B1,SMALL(IF(C2:C11>2000,A2:A11),ROW(INDIRECT("1:"&COUNTIF(C2:C11,">2000")))),))

STEP3# Press CTRL + SHIFT +ENTER shortcut keys to convert the above formula into an array formula.

Let’s see how the above formula works.

=IF(C2:C11>2000,A2:A11)

First use the IF function to determine whether the salary meets the conditions, if the salary is greater than $2000, then return the employee’s ID, otherwise return the logical value FALSE.

=ROW(INDIRECT(“1:”&COUNTIF(C2:C11,”>2000″)))

The COUNTIF function is used to calculate the number of scores greater than 100 and is combined with the ROW function and INDIRECT function to generate a sequence of natural numbers from 1 to n.

=SMALL(IF(C2:C11>2000,A2:A11),ROW( INDIRECT(“1:”&COUNTIF(C2:C11,”>2000″))))

Use the SMALL function to find the employee number whose salary is greater than $2000 and return the following memory array.

={1;3;5;10}

=OFFSET(B1,{1;3;5;10},)

The OFFSET function extracts the employee’s name from the result returned by the SMALL function and returns the following array of employee names.

={"Tom";"Jacey";"William";"nicole"}

=T(OFFSET(B1,{1;3;5;10},))

Finally, the T function is used to convert the multi-dimensional reference returned by the OFFSET function into a memory array.

4. Extracting Sub-array from a two-dimensional Array

The cell range A1:C10 contains data of text and numeric type, see the figure below.

If you want to extract all the text-based data from the specified cell range A1:C10, then you can use the following array formula.

=T(INDIRECT(TEXT(SMALL(IF(A1:C10>="",ROW(A1:C10)/1%+COLUMN( A1:C10)),ROW(INDIRECT("1:"&COUNTIF(A1:C10,"*")))), "r0c00"),))

Let’s see how the above formula works.

=IF(A1:C10>=””,ROW(A1:C10)/1%+COLUMN( A1:C10))

The IF function is used to determine the type of data in the cell range. If the cell value is the text, then let the cell’s line number multiplied by 100, and then add the cell’s column number, and then return a numeric result; if the cell value is not text type, then return the logical value FALSE.

={FALSE,102,FALSE;FALSE,202,FALSE;FALSE,302,FALSE;FALSE,402,FALSE;FALSE,502,FALSE;FALSE,602,FALSE;FALSE,702,FALSE;FALSE,802,FALSE;FALSE,902,FALSE;FALSE,1002,FALSE}

=ROW(INDIRECT(“1:”&COUNTIF(A1:C10,”*”)))

The COUNTIF function is used to calculate the number of text values in the range of cells A1:C10, and combined with the ROW function and INDIRECT function to generate a series of natural numbers from 1 to n.

={1;2;3;4;5;6;7;8;9;10}

=SMALL(IF(A1:C10>=””,ROW(A1:C10)/1%+COLUMN( A1:C10)),ROW(INDIRECT(“1:”&COUNTIF(A1:C10,”*”))))

The SMALL function is used to extract the position information of the cell where the text is located and return a memory array.

={102;202;302;402;502;602;702;802;902;1002}

=INDIRECT(TEXT({102;202;302;402;502;602;702;802;902;1002}, “r0c00”),)

The TEXT function is used to convert the location information to R1C1 reference style, and then use the INDIRECT function to return to the cell reference.

=T(INDIRECT(TEXT({102;202;302;402;502;602;702;802;902;1002}, “r0c00”),))

Finally, the multi-dimensional references returned by the INDIRECT function are converted to memory arrays using the T function.

5. Fill the Merged Cells by Array Formula

In the merged cells, only the first cell has a value, while the rest of the cells are empty cells. When we work with the data, we may need to fill the empty cells in the merged cells with the corresponding values to meet the needs of the calculation.

The following is a product sales table, we need to fill the empty cells in the merged cells with the corresponding region name. The data table is as follows:

You can fill the data into the merged cells by using the following array formula.

=LOOKUP(ROW(A2:A13),ROW(A2:A13)/(A2:A13>""),A2:A13)

Let’s See How This Formula Works:

=ROW(A2:A13)/(A2:A13>””)

This formula assigns a non-empty cell in column A to the row number of that cell, and returns the error value #DIV/O! for empty cells, and finally returns a memory array.

{2;#DIV/0!;#DIV/0!;#DIV/0!;6;#DIV/0!;#DIV/0!;#DIV/0!;10;#DIV/0!;#DIV/0!;#DIV/0!}

Finally, the LOOKUP function is used to perform a fuzzy search and return the corresponding region name.

6. Convert Two-dimensional Array to one-dimensional Array

Some functions only support one-dimensional array as their arguments, not two-dimensional array. For example, the second argument of the MATCH function, the second argument of the LOOKUP function, and so on. If you want to complete the query in a two-dimensional array, you need to first convert the two-dimensional array to a one-dimensional array.

In the figure below, the cell range A1:C4 is a two-dimensional array, by using the following formula you can return the maximum value less than or equal to 100 in the cell range; LOOKUP function will perform a fuzzy search from a one-dimensional array returned by the SMALL formula, and return the value that matches the conditions.

The formula is as follows.

=LOOKUP(100,SMALL(A1:C4,ROW(1:12)))

Let’s see how this formula works:

=SMALL(A1:C4,ROW(1:12))

Because the cell range is 4 rows and 3 columns, it is a two-dimensional array containing 12 elements. You can generate a sequence of natural numbers from 1 to 12 by using the ROW function. Then use the SMALL function to sort the two-dimensional array and return a one-dimensional memory array. The result is as follows:

={16;60;78.6;79;97;97;99;#NUM!;#NUM!;#NUM!;#NUM!;#NUM!}

The LOOKUP function performs a fuzzy lookup by row and ignores the error value #NUM!. Finally, the maximum value less than or equal to 100 is returned, which is 99.

February 19, 2023February 19, 2023

How to Generate Random Passwords in Excel

This post will teach you how to quickly generate a random password with Formula in Excel. You can combine a series of worksheet functions to create a complex formula to generate a random password in Microsoft Excel. How do I create an random code generator in Excel.

1. Video: Generate Random Passwords or Letters in Excel

This tutorial video provides step-by-step instructions on how to use Excel’s built-in functions to generate random passwords or letters in Excel, making it easy to create secure and unique passwords for a variety of purposes.

2. Generate Random Password

If you want to generate a random and secure password in Excel, you can use a combination of the CHAR function, the RANDBETWEEN function and concatenation operator to build a new Excel formula.

Just use the following steps:

Step1: Select one cell that you want to generate the random password,

Step2: then enter this formula in that Cell

=CHAR(RANDBETWEEN(65,90))&CHAR(RANDBETWEEN(97,122))&CHAR(RANDBETWEEN(97,122))&CHAR(RANDBETWEEN(65,90))&RANDBETWEEN(100,999)&CHAR(RANDBETWEEN(42,43))

Step3: press Enter key to apply this formula. You will see that it will generate an eight characters password.

3. Random Letter Generator in Excel

If you want to create a series of random letters or strings in Microsoft Excel Spreadsheet. and you can use built-in functions such as the CHAR function, which can convert a number to its corresponding ASCII character.

For example, to generate a 5-character string of uppercase letters, and you can use the following formula based on the CHAR function and the RANDBETWEEN function.

=CHAR(RANDBETWEEN(65,90))&CHAR(RANDBETWEEN(65,90))&CHAR(RANDBETWEEN(65,90))&CHAR(RANDBETWEEN(65,90))&CHAR(RANDBETWEEN(65,90))

How to Generate Random Passwords in Excel 1.png

This formula generates a random uppercase letter for each position in the string. By copying and pasting the formula into multiple cells, users can generate a series of unique random strings for various purposes, such as generating temporary passwords or unique IDs.

4. Generate Random Uppercase Letter

If you want to generate a random password only contain alpha characters (random uppercase letter string), you can use the following formula:

=CHAR(RANDBETWEEN(65,90))

5. Generate Random Lowercase Letter String

If you want only to generate a random lowercase letter string, you can use the below formula:

=CHAR(RANDBETWEEN(97,122))

6. Generate Random Password Only Contain Numeric Characters

If you want to generate a random password only contain 6-digit numeric characters, you can use the following formula:

=RANDBETWEEN(100000,900000)

7. Generate Random Symbol String

If you want to generate a random symbol string, just use the following formula:

=CHAR(RANDBETWEEN(33,47))

This formula will randomly generate one of the following specific characters: !”#$%&'()*+,-./

We talked that the CHAR function will generate lots of specific characters, and if you just want to create a password that can be type on the keyboard, so you should keep the specific character simple and avoid to use characters like ¶, Œ, or ©.

8. Conclusion

Generating random passwords in Excel can be a quick and convenient way to create secure and unique passwords for various purposes. By using a combination of built-in functions, users can generate passwords of different lengths and complexity levels, as well as customize them to include different character sets.

9. Related Functions

Excel CHAR function
The Excel CHAR function returns the character specified by a number (ASCII Value).The CHAR function is a build-in function in Microsoft Excel and it is categorized as a Text Function. The syntax of the CHAR function is as below:=CHAR(number)….
Excel RANDBETWEEN Function
The Excel RANDBETWEEN function returns a random integer number that is between the numbers you specify.The syntax of the RANDBETWEEN function is as below:= RANDBETWEEN (bottom,top)….

September 22, 2020October 22, 2022

How to Generate Random Values by a List in Different Cases in Excel

Sometimes we may create some fake data for testing or some other purpose. If we want to create an amount of numbers, we may feel it is annoying by entering random numbers one by one. For this instance, we can use RANDBETWEEN function to create a lot of numbers between a certain range quickly. On the other side, sometimes we may want to create random values refer to a given list, for example select some random numbers from a given number list, you can also select objects by given list as well. This article will show you the way to generate random values, and also generate values from a given list.

Method 1: Generate Random Numbers by RANDBETWEEN Function

Step 1: Select a range for saving the random numbers. Select a blank cell as the first cell in your selected range. Then enter the formula =RANDBETWEEN(1,100). In this case, we will generate numbers between 1 and 100.

Step 2: Press Enter. We get a random number 57. Just between our specified numbers.

Step 3: Drag the fill handle to fill the selected range. Now we create a range of random data. You may notice that the first cell value is changed. That’s because the formula is applied to other cells so after releasing your mouse to complete the task, it was recalculated.

Method 2: Generate Random Numbers by a Given List

Prepare a list with fruit.

Step 1: Select a blank cell, enter the formula =INDEX($D$2:$D$11, RANDBETWEEN(2, 11)). In this formula $D$2:$D$11 is the list area, 2 is the first row number for the area, and 11 is the last row number for the area. You can change parameters per your demands.

Step 2: Press Enter to check result. Verify that the random value is generated properly and it is also listed in original fruit list.

Step 3: Drag the fill handle down till F6. Verify that fruits are generated by random.

NOTICE:

In this step, when dragging fill handle to fill other cells, some errors may occur, for example:

You just need to double click on error cell and re-apply the formula.

Sometimes the random values may be duplicate. See above screenshot, ‘Pear’ are displayed twice.

Method 3: Generate Random Numbers by a Given List Between Certain Range

Prepare a list with numbers. If we want to generate random values from this list and at the same time between number 1 to 50, how can we do? Let’s see steps below.

Step 1: Select a blank cell, enter the formula =INDEX($A$1:$A$14, RANDBETWEEN(1,50)). Then press Enter to check if the generated number matches the two criteria.

How to Generate Random Values in Excel10

Obviously, number 37 is between range 1 to 50, and it is also listed in original list.

Related Functions

Excel INDEX function
The Excel INDEX function returns a value from a table based on the index (row number and column number)The INDEX function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the INDEX function is as below:= INDEX (array, row_num,[column_num])…
Excel RANDBETWEEN Function
The Excel RANDBETWEEN function returns a random integer number that is between the numbers you specify.The syntax of the RANDBETWEEN function is as below:= RANDBETWEEN (bottom,top)….

February 18, 2019

How to Pick a Random Name from a List in Excel

This post will guide you how to pick a random name from a list in Excel. How do I select random value from a list or a table with a formula in Excel 2013/2016.

Pick Random Name from a List

Assuming that you have a list of names in range B1:B6, and you want to pick random names from this list. How to do it. You can use a formula based on the INDEX function, the RANDOMBETWEEN function and the COUNTA function to achieve the result. You can use one of the following formulas:

=INDEX($B$2:$B$6,RANDBETWEEN(1,COUNTA($B$2:$B$6)),1)

=INDEX($B$2:$B$6,RANDBETWEEN(1,ROWS($B$2:$B$6)),1)

Type this formula into a blank cell and press Enter key on your keyboard. And then drag the Fill Handle down to list the random names that you need. And then press F9 key, it will generate the different name list randomly.

Note: you should know that the name list will be changed when you refresh the current worksheet.

Related Functions

Excel INDEX function
The Excel INDEX function returns a value from a table based on the index (row number and column number)The INDEX function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the INDEX function is as below:= INDEX (array, row_num,[column_num])…
Excel COUNTA function
The Excel COUNTA function counts the number of cells that are not empty in a range. The syntax of the COUNTA function is as below:= COUNTA(value1, [value2],…)…
Excel RANDBETWEEN Function
The Excel RANDBETWEEN function returns a random integer number that is between the numbers you specify.The syntax of the RANDBETWEEN function is as below:= RANDBETWEEN (bottom,top)….

January 21, 2019

How to Generate Random Numbers between Two Numbers in Excel

This post will guide you how to generate random numbers between two numbers in Excel. How do I generate random numbers between two numbers without duplicates with VBA macro in Excel.

Generate Random Numbers between Two Numbers with Formula
Generate Random Numbers between Two Numbers with VBA

Generate Random Numbers between Two Numbers with Formula

If you want to generate random numbers between two given numbers, you can use a formula based on the RANDBETWEEEN function in Excel.

For example, you need to generate random numbers between 10 and 100 numbers, you can use the following formula:

=RANDBETWEEN(10,100)

Type this formula into a blank cell, and press Enter key on your keyboard, and then drag the AutoFill handle to other cells to generate more random numbers.

If you want to generate random number between 0 and 1, you can directly use the RAND function. Like this:

=RAND()

Generate Random Numbers between Two Numbers with VBA

You can also use VBA code to define a new function to achieve the same result of generating random numbers with 2 decimal places between two given numbers in Excel. Or you can generate random numbers with given decimal places in a given range of cells in Excel. Here are the steps:

#1 open your excel workbook and then click on “Visual Basic” command under DEVELOPER Tab, or just press “ALT+F11” shortcut.

#2 then the “Visual Basic Editor” window will appear.

#3 click “Insert” ->”Module” to create a new module.

#4 paste the below VBA code into the code window. Then clicking “Save” button.

Public Function RandomRealNumbers(lowNum As Long, highNum As Long, _
Optional decN As Integer)
   If IsMissing(decN) Or decN = 0 Then
      Randomize
      RandomNumbers = Int((highNum + 1 - lowNum) * Rnd + lowNum)
   Else
      Randomize
      RandomNumbers = Round((highNum - lowNum) * Rnd + lowNum, decN)
   End If
End Function

#5 back to the current worksheet, then type the following formula in a blank cell, and then press Enter key.

=RandomRealNumbers(10,100,2)

#6 drag the AutoFill handle over other cells to generate random numbers with two decimal places between two given numbers 10 and 100.

Related Functions

Excel RANDBETWEEN Function
The Excel RANDBETWEEN function returns a random integer number that is between the numbers you specify.The syntax of the RANDBETWEEN function is as below:= RANDBETWEEN (bottom,top)….

August 19, 2018January 21, 2019

Randomly Select Cells

This post will guide you how to randomly select cells from a list or table in Excel. How to get random value form a range of cells in an Excel Spreadsheet.

Assuming that you have a list of data (B1:B7) in a worksheet, and you want to get 3 random cells or values from those data, how to achieve it. This post will introduce two method to get random value from a range of cells. Randomly Select Cells with Formula. Or how to use an Excel User Defined Function to select cells randomly.

Randomly Select Cells with a Formula

If you want to randomly select cells from a range of cells, you can use a formula based on the INDEX function, the RANDBETWEEN function and the Rows function. Like this:

=INDEX($B$1:$B:$7,RANDBETWEEN(1,ROWS($B$1:$B:$7)),1)

Type this formula into a blank cell C1, and press Enter key in your keyboard, and then drag the AutoFill Handle over other two cells to apply this formula to get 3 random cells.

Randomly Select Cells with a User Defined Function

You can also write an User Defined Function with VBA code to quickly select random cells from a range of cells in Excel. Just do the following steps:

#1 open your excel workbook and then click on “Visual Basic” command under DEVELOPER Tab, or just press “ALT+F11” shortcut.

#2 then the “Visual Basic Editor” window will appear.

#3 click “Insert” ->”Module” to create a new module.

#4 paste the below VBA code into the code window. Then clicking “Save” button.

Function RandomSelectCells(R As Range)
 Dim i As Integer
 Randomize
 i = Int(R.Count * Rnd + 1)
 RandomSelectCells = R.Cells(i).Value
 End Function

#5 back to the current worksheet, try to enter the below formula in Cell C1.

=RandomSelectCells($B$1:$B$7)

Related Functions

Excel INDEX function
The Excel INDEX function returns a value from a table based on the index (row number and column number)The INDEX function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the INDEX function is as below:= INDEX (array, row_num,[column_num])…
Excel RANDBETWEEN Function
The Excel RANDBETWEEN function returns a random integer number that is between the numbers you specify.The syntax of the RANDBETWEEN function is as below:= RANDBETWEEN (bottom,top)….
Excel ROWS function
The Excel ROWS function returns the number of rows in a cell reference.The syntax of the ROWS function is as below:= ROWS(array)…

August 14, 2018August 14, 2018

Generate Random Character Strings

This post will guide you how to generate a random character string in excel. How do I create a random passwords, text strings with a formula in excel. How do I generate random strings of characters with VBA code in excel. How to generate random alphanumeric values in excel.

Generate Random Character Strings with Formula

To generate random character strings in excel, you should be generate random numbers, letters, and special characters. We have talked that how to generate a random number string with Excel RANDBETWEEN function in excel.

Generate random Number

To generate random number string, you can use a formula based on the RANDBETWEEN function. like this:

=RANDBETWEEN(0,9)

This function will generate a random number between 0 and 9.

Generate random Letter String

To generate random uppercase or lowercase letter string in excel, you can create a formula base on the CHAR function and the RANDBETWEEN function. like this:

=CHAR(RANDBETWEEN(65,90))

=CHAR(RANDBETWEEN(97,122))

The first formula will generate random uppercase letter between A and Z.

The second formula will generate random lowercase letter between a and z.

Generate Random Specific Character

To generate random specific character in excel, you can use the below formula:

=CHAR(RANDBETWEEN(33,47))

This formula will generate random specific characters, such as: #$!/

Generate random 5-digit number

To generate a random 5-digit number,you can use the following formula:

=RANDBETWEEN(10000,99999)

Generate Random Three Letters String

We have know how to create a random uppercase letter and lowercase letter, if you want to create a random 3-letters string, you can use the following formula:

=CHAR(RANDBETWEEN(65,90))& CHAR(RANDBETWEEN(65,90)) & CHAR(RANDBETWEEN(65,90))

Generate Random Character String with VBA Code

You can also use an VBA Macro Code to generate a random character string in a selected cell in Excel. You just need to do the following steps:

#1 open your excel workbook and then click on “Visual Basic” command under DEVELOPER Tab, or just press “ALT+F11” shortcut.

#2 then the “Visual Basic Editor” window will appear.

#3 click “Insert” ->”Module” to create a new module

#4 paste the below VBA code into the code window. Then clicking “Save” button.

Sub createRandomString()
    Dim rString As String
    Dim iCount As Integer
    Randomize
    For iCount = 1 To 5
        If Int((2 * Rnd) + 1) = 1 Then
            rString = rString & Chr(Int(26 * Rnd + 65))
        Else
             rString = rString & Int(10 * Rnd)
        End If

    Next iCount
    ActiveSheet.Range("B2").Formula = rString
End Sub

#5 back to the current worksheet, then run the above excel macro. Click Run button.

#6 Let’s see the result.

Related Functions

Excel CHAR function
The Excel CHAR function returns the character specified by a number (ASCII Value).The CHAR function is a build-in function in Microsoft Excel and it is categorized as a Text Function. The syntax of the CHAR function is as below:=CHAR(number)….
Excel RANDBETWEEN Function
The Excel RANDBETWEEN function returns a random integer number that is between the numbers you specify.The syntax of the RANDBETWEEN function is as below:= RANDBETWEEN (bottom,top)….

Generate Random Passwords
To generate a Random and secure password in Excel, you can use a combination of the CHAR function, the RANDBETWEEN function and concatenation operator to build a new Excel formula….

March 30, 2018August 19, 2018

Excel RANDBETWEEN Function

This post will guide you how to use Excel RANDBETWEEN function with syntax and examples in Microsoft excel.

Description

The Excel RANDBETWEEN function returns a random integer number that is between the numbers you specify. Each time when the worksheet is calculated or opened, a new random integer number is generated. So you can use the RANDBETWEEN function to generate a random integer number between tow given values.

The RANDBETWEEN function is a build-in function in Microsoft Excel and it is categorized as a Math and Trigonometry Function.

The RANDBETWEEN function is available in Excel 2016, Excel 2013, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000, Excel 2011 for Mac.

Syntax

The syntax of the RANDBETWEEN function is as below:

= RANDBETWEEN (bottom,top)

Where the RANDBETWEEN function arguments are:

bottom -This is a required argument. The smallest integer number that RANDBETWEEN Function will return.
top – This is a required argument. The largest integer number that RANDBETWEEN Function will return.

Note:

If the bottom argument is greater than the given top value, the RANDBETWEEN function will return #NUM! Error.
If either of the given arguments are non-numeric, this function will return #VALUE! Error.

Excel RANDBETWEEN Function Examples

The below examples will show you how to use Excel RANDBETWEEN Function to generate a random integer between two supplied integer numbers.

1# get a random number between 1 and 200, enter the following formula in Cell B1.

=RANDBETWEEN(1,200)

2# get a random number between -10 and 10, enter the following formula in Cell B2.

=RANDBETWEEN(-10,10)

Related Functions

Excel RAND function
The Excel RAND function returns a random real number that is greater than or equal to 0 and less than 1. The syntax of the RAND function is as below:=RAND ()…

More Excel Randbetween Function Examples

Generate Random Character Strings
To generate random character strings in excel, you should be generate random numbers, letters, and special characters. We have talked that how to generate a random number string with Excel RANDBETWEEN function in excel…
Generate Random Passwords
To generate a Random and secure password in Excel, you can use a combination of the CHAR function, the RANDBETWEEN function and concatenation operator to build a new Excel formula….
Randomly Select Cells
If you want to randomly select cells from a range of cells, you can use a formula based on the INDEX function, the RANDBETWEEN function and the Rows function…..

1. Generate Array with ROW or COLUMN Functions

2. Generating two-dimensional Array from one-dimensional Array

a. One-dimensional range rearrangement to generate two-dimensional array

b. Combining two columns of data to create a two-dimensional array

3. Extracting Sub-arrays From Data

4. Extracting Sub-array from a two-dimensional Array

5. Fill the Merged Cells by Array Formula

6. Convert Two-dimensional Array to one-dimensional Array

1. Video: Generate Random Passwords or Letters in Excel

2. Generate Random Password

3. Random Letter Generator in Excel

4. Generate Random Uppercase Letter

5. Generate Random Lowercase Letter String

6. Generate Random Password Only Contain Numeric Characters

7. Generate Random Symbol String

8. Conclusion

9. Related Functions

Method 1: Generate Random Numbers by RANDBETWEEN Function

Method 2: Generate Random Numbers by a Given List

Method 3: Generate Random Numbers by a Given List Between Certain Range

Related Functions

Pick Random Name from a List

Related Functions

Generate Random Numbers between Two Numbers with Formula

Generate Random Numbers between Two Numbers with VBA

Related Functions

Randomly Select Cells with a Formula

Randomly Select Cells with a User Defined Function

Related Functions

Generate Random Character Strings with Formula

Generate Random Character String with VBA Code

Related Functions

Related Posts

Description

Syntax

Excel RANDBETWEEN Function Examples

Related Functions

More Excel Randbetween Function Examples