I am trying to write a macro to deal with the large amount of data I
get back from a monitoring instrument. The sensor samples at a rate of
once per second for 60 seconds and then goes to sleep for 14 minutes.
The data spans over two weeks so I have over 65,000 data points. What I
want to do is average the 60 reading I get at each sampling. I have
imported it into excel and I have recorded a macro for the first 600
data points but that's far from running the complete data set.

I have used the code below to get this far by manually replacing the
Range value with the next set to be averaged, ie. D120, D180 ect. How
can this be designed to increment the range automatically or by running
a loop until it no longer finds data to use. I do have a simple macro
to count the rows of data so the output from that could be used tell
the macro when to stop.

Range("D60").Select
ActiveCell.FormulaR1C1 = "=AVERAGE(R[-59]C[-1]:RC[-1])"

Ideally it would be nice to have a dialog box that asked how rows are
to be averaged so it would be flexible if I change the sampling time in
the future.

Thanks for any help anyone can offer.

Kevin

John Bundy wrote:
Depending on the layout, the Data-Subtotal method would be the easiest. If
each sample has say a name then at each change in that name average the data
column. Other than that an array can loop through every 60 and get an average
but I need to know how it is layed out e.g each sample is in one row and
column or seperate columns/sheets.


Thanks for the response. The data is a three digit number, one per row,
in one column and on the same sheet.

Cheers,
Kevin