Complex query: mean with respect to time rather than fixed number of cells

[thoabb]

I have a workbook with time in one column and a variable in another column. The variable does not occur regularly. So there are sometimes several variables per second and then, perhaps, a gap of a few seconds. There are tens of thousands of row in my workbook. I need a macro that will take a mean of the variable over a constant time period (say 10 secs), rather than a constant number of rows. This macro should be able to repeat itself until it reaches the bottom of the work book and the output should be recorded in consecutive rows somewhere else in the workbook or in a separate workbook. I have attached an example of the raw data.macro test file.xlsx

Any help would be appreciated.

Thanks in advance.

[nathansav]

Use SUMIF & COUNTIF So something like sumif(time column,<a1+TIMEVALUE("00:00:10"))/countif(time column,<a1+TIMEVALUE("00:00:10"))

[thoabb]

Thank you for your help - How do I then get that to repeat?

[nathansav]

sumif(time column,<a1+TIMEVALUE("00:00:10"))/countif(time column,<a1+TIMEVALUE("00:00:10"))


you'd need to do SUMIFS next, so it shows when > the last check and less than
sumifs(time column,>a1+TIMEVALUE("00:00:10"),time column,<a1+TIMEVALUE("00:00:20"))/)/countif(time column,<a1+TIMEVALUE("00:00:10"))


I'll take a look at your test file a bit later.

[thoabb]

Thank you very much