Maximum and minimum tide levels for every day

[Dave T]

Hello All,In the attached workbook I have records taken from a data logger that records the movements of a lake level taken every 15 mins.Depending upon tide times the lake level may reach a maximum height twice a day or correspondingly may have two minimum levels per day.From the data in the attached workbook I would like to get the two maximum levels per day if there are two high tides in the same day (i.e. maximum or minimum heights would be at least 10 hours apart). Correspondingly I would also like to get the two minimum levels per day if there are two low tides in the same day.The records consist of two columns:Column A = Date + time (e.g. 1/10/2012 8:04:14 AM)Column B = Lake Level (e.g. 1242)In the attached workbook I have only included a small subset of the data (10 days from October 2012) but the original Excel file contains a years’ worth of records and this totals 28,900 rows.For example the month of October 2012 has 64 records and the maximum two heights are: 1242 on 01/10/2012 at 8:04 AM and 1218.9 on 01/10/2012 at 07:19 PM and the two minimum heights are: 1092.033333 on 01/10/2012 at 1:49 PM and 1102.433333 on 01/10/2012 at 11:49 PAMSo it is probably fair to say that in one day there could either be two high tides and one low tide or two low tides and one high tide.If anyone has any ideas as to how this can be achieved it would be greatly appreciated.Regards,Dave T

[SamT]

With 100 post I assume you have some VBA experience and this is fairly easy.

Sort Tides Descending.
Count Dates by Day(s) until 365. That should return ~ 700 rows.
Copy to new sheet

Sort Tides Ascending
Repeat.

[p45cal]

the month of October 2012 has 64 records and the maximum two heights are: 1242 on 01/10/2012 at 8:04 AM and 1218.9 on 01/10/2012 at 07:19 PM and the two minimum heights are: 1092.033333 on 01/10/2012 at 1:49 PM and 1102.433333 on 01/10/2012 at 11:49 PAMSo it is probably fair to say that in one day there could either be two high tides and one low tide or two low tides and one high tide.If anyone has any ideas as to how this can be achieved it would be greatly appreciated.Regards,Dave TI think you mean the 1st October has 64 records, not the month of October. Anyway, you say what the two highest values are, but be aware that the first one you give is the very first record in the data, and so while its value might be the highest. it may not be the high tide because for it to be a high tide you need to know that there are lower readings both before and after that reading, but with the very first reading you don't know whether the reading before were higher or lower than that first reading. That aside, looking at the data, a simple formula approach might give you what you need; looking at the data, it seems safe to say that if a value is higher or equal to any of the 5 readings before it and after it (that is it's the highest of the 11 readings it's in the middle of) you can take it that it's a high tide. If you feel 5 readings either side isn't enough (6 readings represent 1 and a quarter hours) you can always increase that, but to no more than 3 hours either side.
So to identify the high and low tides you can add two columns of formulae; on your sheet I added this formula to cell J9 and copied down:
=IF($B9=MAX($B4:$B14),$B9,"")
and this formula to K9 and copied down:
=IF($B9=MIN($B4:$B14),$B9,"")
You'll sometimes see two equal readings 15 minutes or 30 minutes apart which means that the high/low tide was somewhere between those two times. If you're not worried about the exact time of a high/low tide then you can just take the height value.
Will this sort of thing do?

ps. there is one major assumption in the above formulae, that is that there are no (or very few) missing readings, nor any gaps in the data (same thing really).

[mikerickson]

One way to look for extrema is to look at how the data is changing.
If the tide is rising B(n)<B(n+1) and B(n+1)-B(n) > 0
If the tide is falling B(n)>B(n+1) and B(n+1)-B(n) < 0

What you are interested in is when it changes from rising to falling, i.e. high tide (or vice versa at low tide)

Put a helper column with =SIGN((B3-B2)*(B4-B3)) in J2 and drag down.
When the J value is -1, then you have an extrema, either a high tide or a low tide.

Those are the values that you can inspect to see if there are two per day or not.

[Dave T]

Thanks for the replies SamT, p45cal & mikerickson,

Firstly my apologies for the late reply, but I post this question on behalf of a work colleague and I have yet to further feedback from him.
However I appreciated the different responses each of you gave…

p45cal: I thought what you suggested was a very clever solution. I did initially wonder why you said to place your formulas starting in cells J9 and K9, but when I copied the formula down the MAX part in the brackets was suddenly very clear – simple and effective (very nice)

mikerickson: Your solution was useful too.

SamT: I must admit I found your reply a bit hard to follow. Sort ‘tides’ descending is OK, but not sure about ‘Count Dates by Day(s) until 365’, I assume you mean =DATEDIF(StartDate,A4+1,"d") which gives me the numerical day of the year, is this correct. The rest was also a bit confusing i.e. how could the next part ‘Sort tides descending’ and ‘Sort tides ascending’ return either two high tides or two low tides in the same day.

Unless I get more questions from my work colleague I will mark this as solved.

Once again, thank you all for your replies.

Regards,
Dave T

[Dave T]

Hello SamT, p45cal & mikerickson,

My work colleague has got back to me and is very happy with the solution posted by both p45cal & mikerickson, but found the solution by p45cal worked better.

He would like to thank you all for your help.

Regards,
Dave T

[SamT]

:thumb

[Paul_Hossler]

One way to look for extrema is to look at how the data is changing.
If the tide is rising B(n)<B(n+1) and B(n+1)-B(n) > 0
If the tide is falling B(n)>B(n+1) and B(n+1)-B(n) < 0


Q: Aren't

B(n)<B(n+1) and B(n+1)-B(n) > 0 the same?

The second would equate to B(N+1) > B(n) which is the same, isn't it?


Also, do you need to worry about the 'noise'? For example, at 10/1/2012 2:49:14 PM the data goes from down for one reading and and then back up



Paul

[mikerickson]

Paul, yes the first sentence could be heard as

"If the tide is rising,
B(n)<B(n+1)
and (therefore) B(n+1)-B(n) > 0"

And yes the noise would effect things, but the OP data isn't that noisy.