how to pull MAX values out of date-time groupings over a year

Hi. New to the Forum so forgive me if this question has been posted before. I have multiple years of 6-minute interval tidal depth data from a NOAA tide gauge. I need to calculate the mean, median, max inundation depth over a set of known elevations, so have filtered my data to give only those dates/times in which the tidal depth surpassed my known elevations. For each period of inundation, I need to extract the maximum tidal height.

For example, the periods below are just two periods of consecutive 6 minute tide gauge readings (4/6/2012 & 5/6/2012) in which the tide level (right column) exceeded my elevation of interest (1.1101). At this elevation I would typically have about 6-8 periods of inundation per year. I now need to pull out the maximum tide level for each of these periods so I can calculate a mean inundation level at this elevation. For higher elevations such as this example it's easy enough to manually select the groupings and use the MAX function, but for lower elevations that are flooded twice daily 365 days a year, the amount of data is enormous. Is there a macro or series of if/then statements that could be used to recognize these periods and then pull the max values from each?

Thanks,
StellaBean

4/6/2012 19:42 1.137
4/6/2012 19:48 1.168
4/6/2012 19:54 1.194
4/6/2012 20:00 1.219
4/6/2012 20:06 1.241
4/6/2012 20:12 1.26
4/6/2012 20:18 1.276
4/6/2012 20:24 1.289
4/6/2012 20:30 1.297
4/6/2012 20:36 1.301
4/6/2012 20:42 1.3
4/6/2012 20:48 1.293
4/6/2012 20:54 1.284
4/6/2012 21:00 1.273
4/6/2012 21:06 1.258
4/6/2012 21:12 1.239
4/6/2012 21:18 1.215
4/6/2012 21:24 1.188
4/6/2012 21:30 1.16
4/6/2012 21:36 1.132
5/6/2012 19:42 1.116
5/6/2012 19:48 1.153
5/6/2012 19:54 1.187
5/6/2012 20:00 1.218
5/6/2012 20:06 1.247
5/6/2012 20:12 1.272
5/6/2012 20:18 1.293
5/6/2012 20:24 1.309
5/6/2012 20:30 1.321
5/6/2012 20:36 1.331
5/6/2012 20:42 1.339
5/6/2012 20:48 1.343
5/6/2012 20:54 1.342
5/6/2012 21:00 1.334
5/6/2012 21:06 1.324
5/6/2012 21:12 1.309
5/6/2012 21:18 1.29
5/6/2012 21:24 1.266
5/6/2012 21:30 1.24
5/6/2012 21:36 1.213
5/6/2012 21:42 1.187
5/6/2012 21:48 1.16
5/6/2012 21:54 1.135

Hey StellaBean,

I think I have found a crude but simple solution. In the attached spreadsheet I created a simple filter that will allow you to enter the minimum tide level you wish to see and it will give you all of the values that are greater than or equal to that number in cell C2 listed in Column E. All of the values you want to see are then calculated only from Column E. I am sure there is a much better way of doing this but unfortunately I am not the brightest crayon in the box...

Hope this helps!


Simeon

oeldere,

I like yours more! I completly forgot about pivot tables...
I was spending a little time writing long formulas using arrays when I didn't even have to!

Well done.


Simeon

oeldere,

Thanks so much for the help! Never used a Pivot Table before so need to look it over and digest what it's doing. Thanks to you and simeon.hruby both for the help!

StellaBean