Random number from fixed set of options

In Excel, you can return the random number from a fixed set of options based on bottom and top range of values. For this purpose, you need to use a formula based on RANDBETWEEN and CHOOSE functions, because RANDBETWEEN function returns the random number that is between a bottom and top range of given values, like between 1 to 10, and CHOOSE function returns a number from given set of values based on given position.

Random number from a fixed set of options

In this formula, RANDBETWEEN function will return a random position between the specified bottom and top values and CHOOSE function will return a random number from a fixed set of options or values, based on random position returned by RANDBETWEEN function.

The formula

The syntax for the Excel CHOOSE function is:

=CHOOSE( position, value1, [value2, ... value_n] )

And syntax for the Excel RANDBETWEEN function is:

=RANDBETWEEN( bottom, top )

So, the generic formula to return the random number from the fixed set of options will be;

=CHOOSE(RANDBETWEEN(bottom,top), value1, [value2, ... value_n] )

Here, Excel RANDBETWEEN function replaces position argument inside CHOOSE function to return a random position based on which random number from the fixed set of options or values is returned by CHOOSE function.

Example

Suppose you have a fixed or specified set of values, like 20, 25, 30, 40, 50 and you want to return from these values a random number from a fixed set of options or set of values to fill the range of cells in Excel. As you have 5 values in a fixed set of options, so the formula needs to return a random position between 1 and 5, bottom and top values, using RANDBETWEEN function, such as;

=CHOOSE(RANDBETWEEN(1,5),20,25,30,40,50)

Apply this formula in a cell-like A1 and then drag or copy paste to the range of cells to fill with a random number from the fixed set of options.

Figure 1.Random fixed set of options

Please note that Excel RANDBETWEEN function returns different random values in cells range whenever any cell containing formula is edited or worksheet is opened. If you want to have values once populated by this formula then you need to replace the formulas with values to prevent further changes. You need to copy the range of cells containing formulas > mouse right click > paste special > values to replace formulas with values.

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar