Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

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 specials -> values in the original column

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

Are there better ways?

share|improve this question

2 Answers 2

up vote 2 down vote accepted

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

Let me know how this goes.

Also see my RAND() vlookup spreadsheet.

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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