1

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?

2

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

1

With a little script you can do that automatically. Here's how.

Code

function myFill(rows, columns, range) {
  var output1 = [], l = range.length;
  for(var i=0; i<rows; i++) {
    var output2 = [];
    for(var j=0; j<columns; j++) {
      output2.push(range[Math.floor(Math.random() * l)][0]);
    }
    output1.push(output2);
  }   
  return output1;  
}

Screenshot

enter image description here

Example

I've created an example file for you: Create Random Output
Add the script under Tools>Script editor and press the save button.

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.