Aggregate Precipitation Data to 15min Intervals

[dariuzthepole]

Hi,

I have an automatic (tipping bucket) rain gauge that records the time of tip to the nearest second. These are random and do not follow a distinct pattern.

I would like to aggregate the time of tip data to show a 15min cumulative total for that 15min period. The data is laid out as simple as possible: time of tip timestamps in column A; values in column B; 15min timestamps in column C and aggregated 15min values in column D. In my head I'm looking for a function/code that searches for timestamps (column A) within the specified 15min period (column C), then in column D sum the values from column B for the specified rows.

The problem I have is there could be no tips, so no data, or there could be several tips (during heavy rain). Each tip is in its own row. Most of the solutions to similar problems I've found specify a set number of rows, which wouldn't work in this instance.

Any help would be greatly appreciated.

 

[pgc01]

Hi

This is one solution.

I'd use C as an auxiliary column that would calculate the 15min bin. For ex. for time between 2:00:00 and 2:15:00 would have the value 2:00:00, then for times between 2:15:00 and 2:30:00 would have 2:15:00, etc.

Then I'd use a pivot table with columns B and C and I'd automatically get what you want.