I want to generate a column which will have random string values in any order. Those string values have to be from ('New', 'Mint', 'Very Good', 'Good', 'Acceptable', 'Poor') strings? Can anyone let me know about how can I make use of RANDBETWEEN function to populate my columns with the mentioned data in brackets?

  • Can you repeat those strings or can they only appear once? – Andi Mohr Nov 12 '15 at 15:46
  • I have to repeat those strings – Ashutosh Nov 12 '15 at 15:50

If you only have a few strings

If your strings are in the first column you can use the CHOOSE() function like this:

=CHOOSE(RANDBETWEEN(1,6),$A$1,$A$2,$A$3,$A$4,$A$5,$A$6)

RANDBETWEEN(1,6) will randomly pick a number n from 1 to 6 - CHOOSE then counts through all the parameters you've listed (your strings in column A) and displays the nth term.

enter image description here

But, if you have many strings

If you have more than a few items to CHOOSE from, maintaining this formula becomes unwieldy.

In which case it's probably a good idea to insert a Table (let's call it MyStrings), create an ID column in column A using =ROW()-1 and switch our formula to a VLOOKUP. We can use MIN & MAX and structured references to dynamically work out how many terms there are in your table, for use in the RANDBETWEEN:

=VLOOKUP(RANDBETWEEN(MIN(MyStrings[ID]),MAX(MyStrings[ID])),MyStrings,2,0)

enter image description here

Careful Now

RANDBETWEEN is a volatile function, which means that it recalculates every time any other cell changes (anywhere in your workbook). If you need to keep the randomly-generated strings fixed as they are once you've produced them, you'll need to copy and Paste Special as Values.

If you use a lot of volatile functions, it may slow your worbook down, but there are things you can to do alleviate this.

You can use INDEX together with RANDBETWEEN to do this.

=INDEX({"New","Mint","Very Good","Good","Acceptable","Poor"},RANDBETWEEN(1,6))

Just fill this formula down your column.

  • 1
    is this volatile? – Raystafarian Nov 12 '15 at 18:37
  • @Raystafarian yes, because of RANDBETWEEN the value will change each time the sheet is calculated. – Excellll Nov 12 '15 at 18:54
  • I thought so, but nobody mentioned it to the OP. – Raystafarian Nov 12 '15 at 18:55
  • Good point. I'll edit my post to make that point as well. – Andi Mohr Nov 13 '15 at 9:28

Your Answer

 

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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