Randbetween exactly some values in excel ?

**Mr.Nob0OOdy** · 06-30-2012, 12:14 PM

Hello !

In excel there is randbetween(x;y) which display random numbers between exactly given interval. e.g. randbetween(1;23) will give you random number between 1 and 23.
The question is how do you get random numbers from exactly given ones ?

e.g. Let's say you want excel to return random numbers between 1;12;23;35;37;45;53;75;95 and it should return to you only random chosen numbers from this given 'array' like 1 OR 12 OR 23 OR 35 OR 37 OR 45 OR 53 OR 75 OR 95 ONLY ONE number if you want to return only one but if you want it to return more then one then it should be: 1;12 OR 1;35 OR 1;53 if you want to return 2 of them and so on. It should be a formula like:
randbetween("values";"chosen by") eg. randbetween("1;12;23;35;37;45;53;75;95";3) will return to you combo from this array "1;12;23;35;37;45;53;75;95" taken by 3. Like 1;12;23 OR 1;12;53 and so on.

How could I do this in Excel ?

Any help, please ?

Thank you so much !

**shg** · 06-30-2012, 12:16 PM

=index({1;12;23;35;37;45;53;75;95}, randbetween(1,9))

**Mr.Nob0OOdy** · 06-30-2012, 12:20 PM

It return only one number. How to make it to return more than one like 1;12 or 1;23 or 35;95 and so on Or 3 of them or 4 or 5 of them ?

**shg** · 06-30-2012, 12:23 PM

If repeats are acceptable,

= index({1;12;23;35;37;45;53;75;95}, randbetween(1,9)) & ","
& index({1;12;23;35;37;45;53;75;95}, randbetween(1,9))

If not, you could list the numbers down a column, enter =rand() in the adjacent column, sort by the latter, and take as many numbers as you want from the top.

**MarvinP** · 06-30-2012, 12:29 PM

If not, you could list the numbers down a column, enter =rand() in the adjacent column, sort by the latter, and take as many numbers as you want from the top.

This is how I would have done the problem from the start.

**DDUPREEZ** · 06-23-2015, 05:49 AM

I have a different struggle . I use the Randbetween formula, but I need this to stay fixed on the selected random number for each line after the first selection. This enables me to model different scenario's on the constant selected number.

Any Help?