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?
Google Sheets – How to Select Random Data Points
google sheets
Related Topic
- Google-sheets – How to anchor all the cells on the page in Google Spreadsheet
- Google Sheets – Fill Column with Random Values from List
- Google-sheets – How to reference a cell from a Google Spreadsheet in a Google Document
- Google-sheets – Google Spreadsheet generate random numbers with Math.random()
- Google Sheets – How to Repeat X Times When Dragging Down
- Google-sheets – How to select random cells in a row that add up to 300, and count how many it took
- Google-sheets – Linked spreadsheet data to document
- Google-sheets – Protect spreadsheet against duplicate data and sheet addition/deletion
Best 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
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.