Excel Random number from a set of options

Question

In MS Excel, how can I randomly calculate a number number that is from one of a set of 5 options?

For example, in cell B1 I would like to have a random number that is either 15,30,50,75, or 100.

I would like a completely random output of these 5 numbers in cells B1:B100.

I was thinking that I could generate a random number in cell A1 using rand, then using a series of > or < IF statements to output only one of these numbers above.

possible duplicate of [Excel RANDBETWEEN as a string](http://stackoverflow.com/questions/16077013/excel-randbetween-as-a-string) — chris neilsen, Apr 26 '13 at 06:50

score 18 · Accepted Answer · answered Apr 25 '13 at 19:48

18

This formula will do it:

=CHOOSE(RANDBETWEEN(1,5),15,30,50,75,100)

If you want to use a range of cells:

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

answered Apr 25 '13 at 19:48

Doug Glancy

27,214
6
67
115

1

CHOOSE was the perfect solution for me in this case. +1 for the alternative when I may have a range of cells. Thanks – Ryan Apr 25 '13 at 20:15

score 3 · Answer 2 · edited Jun 24 '14 at 19:52

3

A quick and easy way would be to first make a lookup list like this:

enter image description here

Then in your column do a formula like this:

=VLOOKUP(ROUND(RAND()*10,0),$A$7:$B$16,1,FALSE)

where the $A$7:$B$16 is where your list is at. It can be on a different tab, or separate file, if you really need to isolate it.

You could also create a custom VBA function too, but I think that is beyond what you are looking for.

edited Jun 24 '14 at 19:52

Sam R.

16,027
12
69
122

answered Apr 25 '13 at 19:52

Alan Waage

603
4
12

1

hehe.. three solutions posted all at once. I like Doug's better than mine. Upvoted it! – Alan Waage Apr 25 '13 at 19:54

asifrc · Answer 3 · 2013-04-25T19:56:19.183

0

Let's say you have filled rows 1-5 in row G of a spreadsheet with the values you want to randomly display. You can use =INDIRECT("G"&RANDBETWEEN(1,5)) to display any one of those randomly.

`INDIRECT` lets you reference a cell using a string.
Since you want cells "G1" - "G5", we start of with "G".
& combines "G" with the value of the next function.
Then the RANDBETWEEN function will give us a number between the two parameters we provide (in this case 1 through 5).

Let me know if this helps :)

edited Apr 25 '13 at 19:56

answered Apr 25 '13 at 19:49

asifrc

5,781
2
18
22

This is way simpler than the other answers, though I don't think it's reliable in "production" or any large-scale spreadsheet. – Emanuel Vintilă Mar 28 '16 at 14:13

score -1 · Answer 4 · edited Dec 04 '17 at 22:44

-1

How about:

=SMALL({array containing numbers},RANDBETWEEN(1,COUNT({array containing numbers})))

e.g. if you have an array containing the 5 numbers you want to use in $B$2:$B$6

=SMALL($B$2:$B$6,RANDBETWEEN(1,COUNT($B$2:$B$6)))

This returns a random position in the list of numbers, with the total frequency of numbers being defined size of the array.

edited Dec 04 '17 at 22:44

Al-Mothafar

7,949
7
68
102

answered Dec 04 '17 at 18:03

Jorj the Awesome

1

Excel Random number from a set of options

4 Answers4

Linked