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.

[ConneXionLost]

Try these array formulas:

In cell G5

=MAX(IF($A$2:$A$673=$F5,$C$2:$C$673,""))

In cell H5

=MIN(IF($A$2:$A$673=$F5,$C$2:$C$673,""))

Confirm the array formulas with Ctrl-Shift-Enter.

[rlowgren]

Not sure I follow this. Cells G5 and H5 are in the pivot table so I can't enter any formula's in those cells. If I enter it in cell D2, it does not get the desired effect.