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?– user79865Commented Dec 24, 2015 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!– kchakCommented Dec 24, 2015 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.– user79865Commented Dec 24, 2015 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.– user79865Commented Dec 24, 2015 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– kchakCommented Dec 25, 2015 at 4:56
Add a comment
|
1 Answer
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 |
+-------------------------------+-------+-------+
-
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– kchakCommented Dec 25, 2015 at 6:22