Average data for 30 second intervals with different number of data points

Hope someone can help I'm pulling my hair out. I have exercise data taken every few seconds (every breath). I want to average readings over 30 second intervals. It is not as simple as say averaging 30 rows if there was data every second for 30 seconds as the number of data points in each 30 second interval varies depending on breathing rate, ie there might be data at 1 second, 3 seconds, 5 seconds etc. or 2 seconds, 10 seconds, 15 seconds etc.

I therefore need a formula/macro that can look at the column with the timing of the data (breath) mm:ss and pick out the rows between 00:00 and 00:30, 00:31 and 00:59, 01:00 and 01:29... etc. and then average another column for those rows, thus giving me 30 second averages. It's the irregular number of rows that is stumping me and currently I'm selecting them manually.

If anyone has an idea how to solve this I would be grateful!

Originally Posted by shg

Welcome to the forum.


Easy enough if you post a workbook showing the data layout and what you want.

Thanks, I've attached a few columns with the time on the left with a few columns of data I want to average over 30 seconds (vo2, vco2, ve).

In this example the first 30 seconds is the first 8 rows
00:00
00:08
00:11
00:15
00:17
00:21
00:25
00:28
second 30 seconds is next 5 rows and so on
00:32
00:34
00:38
00:45
00:53
I want it to be able to work out which rows to select so I can use it on different studies with different timings.

Hope this is making sense, thanks.

hello,

you can do an extra column where we can write some kind of coding. for example I made a text field containing the hour, the minute and if the seconds are below 30 put 0, or 30 if the seconds >30. Here's the formula i used:

=IF(SECOND(B2)<=30,CONCATENATE(HOUR(B2),MINUTE(B2),"00"),CONCATENATE(HOUR(B2),MINUTE(B2),"30"))

Further you just make a pivot table that has the coding as columns and the average of the time as values

Hope it makes sens.

Thanks for responses so far, not got access to computer at moment so will try the suggestions tomorrow

Originally Posted by DonkeyOte

Could you perhaps use something like:

Please Login or Register  to view this content.

worth adding perhaps that your time values are actually stored as hh:mm rather than mm:ss

Thanks for this it works perfectly! I must admit I'm no excel expert and I don't really understand how the formula works, if you had time to briefly explain the logic that would be great.

With regards to the data being in hh:mm, it seem to automatically be detected like that when I export the txt file with the raw data into excel, I tried changing it to mm:ss in the custom format menu but then your formulas didn't work, it's probably easier to leave it so excel assumes its hh:mm.

Thanks, an elegant solution, not sure I would ever have got there myself!

Thanks again this will save me a lot of time.

This is an old post so hopefully somebody still watches this?
I have a similar problem but my time stamps include date and time - and the time interval is variable. As I need to correlate two temperatures, one was taken every 30 minutes - which I can convert to hourly levels, but the second with variable intervals, I need to standardize (average) the second temperature also to every hour. I attach example data.