Help with tidal data - computing daily max and min of sin curve

[rlowgren]

Hi. I'm trying to calculate tidal datums for a semi-diurnal tide. The data is essentially a sin curve with each day having 2 highs and 2 lows but they are at different elevations (but separated by a fairly consistent time frame approx 6-7 hrs). I need to find each peak and trough value (4 a day). For example I need to calculate the mean high water (MHW) which is the average of every high tide (i.e. every peak of the sin curve).

I know NOAA does this somehow, but I'm sure you can do it in excel too. I used pivot tables to find the max and min each day, but they don't get me the secondary max/min's.

Here's a spreadsheet with some simplified data (1 week). Thanks for any assistance.