Generate random values from a column
To generate a random value from a specific range in a single column you can use a combination of Excel INDEX, RANDBETWEEN and ROWS functions, using Excel and VBA
Example: Generate random values from a column
|
=INDEX(B5:B9,RANDBETWEEN(1,ROWS(B5:B9)),1)
|
This formula uses a combination of Excel INDEX, RANDBETWEEN and ROWS functions to generate a random value from a specific range in a single column. You may select a range that is across multiple columns but would be required to select a specific column number from which to generate a random value.
|
Sub Generate_random_values_from_a_column()
'declare a variable
Dim ws As Worksheet
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'generate a random values from a single column
ws.Range("D5") = WorksheetFunction.Index(Range("B5:B9"), WorksheetFunction.RandBetween(1, ws.Range("B5:B9").Rows.Count), 1)
ws.Range("D5") = WorksheetFunction.Index(Range("B5:B9"), WorksheetFunction.RandBetween(1, ws.Range("B5:B9").Rows.Count), 1)
End Sub
PREREQUISITES
Worksheet Name: Have a worksheet named Analysis.
Range: If using the exact same VBA code, with the same parameters, you need to capture the values that you want to randomly generate in range ("B5:B9").
Worksheet Name: Have a worksheet named Analysis.
Range: If using the exact same VBA code, with the same parameters, you need to capture the values that you want to randomly generate in range ("B5:B9").
ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cell references ("D5") in the VBA code to any cell in the worksheet, that doesn't conflict with the formula.
Range: Select the range from which you want to generate a random value by changing the range ("B5:B9") in the VBA code to any range that doesn't conflict with the formula.
Output Range: Select the output range by changing the cell references ("D5") in the VBA code to any cell in the worksheet, that doesn't conflict with the formula.
Range: Select the range from which you want to generate a random value by changing the range ("B5:B9") in the VBA code to any range that doesn't conflict with the formula.
EXPLANATION
This tutorial shows and explains how to generate random values from a column, by using an Excel formula or VBA.
This tutorial shows and explains how to generate random values from a column, by using an Excel formula or VBA.
Excel Method: This tutorial provides a single Excel method that can be applied to generate random values from a column, using a combination of Excel INDEX, RANDBETWEEN and ROWS functions.
VBA Methods: This tutorial provides a single VBA method that generates random values from a column.
FORMULA
INDEX(range,RANDBETWEEN(1,ROWS(range)),1)
INDEX(range,RANDBETWEEN(1,ROWS(range)),1)
ARGUMENTS
range: A range that captures the values that you want to randomly generate.
range: A range that captures the values that you want to randomly generate.
RELATED TOPICS
| Related Topic | Description | Related Topic and Description |
|---|---|---|
| Generate random numbers | How to generate random numbers between 0 and 1 using Excel and VBA methods | |
| Generate random number between two numbers | How to generate random number between two specific numbers using Excel and VBA methods |
RELATED FUNCTIONS
| Related Functions | Description | Related Functions and Description |
|---|---|---|
| INDEX Function | The Excel INDEX function returns a value that is referenced from a specified range | |
| RANDBETWEEN Function | The Excel RANDBETWEEN function returns a random number between two specified numbers | |
| ROWS Function | The Excel ROWS function returns the number of rows in a specified array |