3

I have a Google spreadsheet document with over 1000 data cells in one column. I want to select 5 random samples from this data set, and copy these to a new column. And repeat this process, n number of times. How do I do this?

  • Do you want sampling with replacement or without replacement? That is, can the previously selected values be selected again? – user79865 Dec 24 '15 at 8:15
  • @Normal Human I think I've figured out with replacement, using VLOOKUP and adding a column for indexes. Then I used RANDBETWEEN to pick search key to be fed into VLOOKUP. If you can give a process for sampling without replacement, I'd appreciate it. Thanks! – kchak Dec 24 '15 at 8:27
  • You don't need a column for indices: just use random OFFSET from the top of the list. Without replacement... I'll have to think about it. – user79865 Dec 24 '15 at 8:33
  • Also, a problem with using randbetween or other random spreadsheet functions is that your samples will be drawn again with every edit to the script. This won't help if you want to analyze the sample. – user79865 Dec 24 '15 at 8:42
  • Yes, I did observe RANDBETWEEN running every time I made a change to the spreadsheet. I'll have a go at randomizer.org – kchak Dec 25 '15 at 4:56
2

To implement sampling without replacement with only spreadsheet functions is not easy. Sampling with replacement is easy enough with randbetween, but if you use it, the samples will change with every edit to the sheet (unless you "stabilize" them by copy-pasting values only, but this is still awkward).

I suggest using an existing solution, such as randomizer.org. It provides a CSV file with random integers in a given range, with or without replacement. The CSV file can be easily imported into Google Sheets: it looks like the table below.

If Randomizer is the name of the sheet with these numbers, and your dataset is in column A of another sheet, then

=offset(A1, Randomizer!A5, 0)

will give the corresponding random element of your list. Copy-paste this command around a 5-by-n range to create the samples.

Note: offset 0 corresponds to the top cell of the data range, so the range of numbers provided to randomizer should begin with 0.

+-------------------------------+-------+-------+
| Research Randomizer Results:  |       |       |
| 20 Sets of 5  Numbers Per Set |       |       |
| Range: From 0 to 999 -- No    |       |       |
| Set 1                         | Set 2 | Set 3 |
| 221                           | 67    | 779   |
| 667                           | 575   | 966   |
| 898                           | 897   | 933   |
| 131                           | 244   | 559   |
| 739                           | 745   | 901   |
+-------------------------------+-------+-------+
| improve this answer | |
  • Thanks. I did see the selected data points changing with every change to the spreadsheet. I didn't think much about it. I'll have a go at randomizer.org – kchak Dec 25 '15 at 6:22

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.