Random Sampling

Question

Hi there

I have 3 years’ worth of gps coordinates of a bird, comprised in a spreadsheet of hourly data collections each day from 4am to 4pm (13 data rows a day and therefore over 13000 rows of data).

I want to randomly select an hour/row of data from each day in order to streamline my data for analysis.

Does anyone know how i would go about setting up a Rand function or similar for selecting one point from each day at random to give me just one point/hour/row for each day rather than 13?

At the moment i do not know how i can organise my data so i can do it all at once rather than having to perform the function separately for each day which is impossible given the size of my dataset.

Help much appreciated!

Answer 1

Hope the screenshot helps

F2 has value 1,

The formula in I2 is copied across to column L

Then copy G2:L2 down to row 3

Now copy F3:L3 down as far as needed (row 1001 ?)

Press F9 to resample

best wishes