Excel Random number from a set of options

问题:

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.

回答1:

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))

回答2:

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

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.

回答3:

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 :)

回答4:

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.