10

Suppose I have a list of strings:

apple, pear, banana, orange

How can I have each cell in a column of cells have a random value from that list?

11

Please try:

=index(A:A,randbetween(1,4))  

where your list of fruits is assumed to be in ColumnA (and consists of four items).

Or without a separate list:

=choose(randbetween(1,4),"apple","pear","banana","orange")
2

Another way, which I prefer, is:

=INDIRECT("A"&RANDBETWEEN(3,7))

Where "A" is the column, and 3,7 is the start and end rows for your list. This way you don't have to figure out how many elements are in your list, you just look at the start and end row numbers, and you don't have to use the whole column for your list. You can have headers and such.

You also can modify this to make it more maintainable, by substituting the last number with the COUNTA function and adding the offset:

=INDIRECT("A"&RANDBETWEEN(4,COUNTA(A4:A99)+3))

The 4 is the first row of the list. The +3 should be 1 less than that number to skip rows not in the range of the list. Give COUNTA a large enough range that you won't have to worry about it for a while, then you can just add items to your list and they'll already be included in your random selection without needing to do anything else. Just ensure the range only includes your list and empty cells.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.