Google Sheets – Fill Column with Random Values from List

google sheets

I need to create some data for a simulation. So I wanted to take a spreadsheet and, for each column, fill it with random data falling within a domain. A uniform distribution is good enough.

For a binary variable, I am currently doing following steps:

  • create a formula like if(rand()>0.5, "black", "white") in a new column
  • fill a column with it, careful to only do it for the amount of rows I want (not just select the whole column and copy into it)
  • copy the results and do paste special -> values in the original column

But if I have a variable with seven possible values, I can't think of anything better than seven nested if statements.

Are there better ways?

Best Answer

I think this is what you want. A spreadsheet the generates a column of rand() numbers. Then look up the rand() number and return another value.

In the following instruction be sure to keep the $s for absolute referencing.

  1. In cell E2 enter the upper limit of the random numbers (I used 7).
  2. In cells C2 to C8 enter the numbers 1 to 7 (since the upper value is 7).
  3. In cells D2 to D8 enter the values you want to return. In this case I used names.
  4. In cell A2 enter the formula: =int(RAND()*$E$2)+1 (where cell E2 holds the upper limit of the random numbers).
  5. Copy this formula down as far as needed.
  6. In cell B2 enter the formula: =vlookup(A2,$C$2:$D$8,2) (where cells C2 to D8 hold the substitution values). If the random number generated is 1 then this returns the name Abe.
  7. Copy this formula down as far as needed.

Final spreadsheet

Related Topic