Calculating time water level below threshold of 2.7m per event

[Bella_75]

Hi

I need to calculate the total time that a water level is below the threshold of 2.7m. I need to do this for each event where this occurs. 

I'm trying to work out the longest period where water is below the 2.7m threshold.

At the moment I am looking at a manual fix but know there is an intelligent way to solve this issue. Can you help me?

Thanks.

[#2 · Accepted solution]

@Bella_75 As with many things Excel there are more than 1 ways to skin the cat.  Here is 1 that I think is pretty 'simple'.  I added this equation to the column next to the depth:

=IF(D2<2.7,C2-C1+E1,0)

It will simply add the latest time span to the previous if the depth is below the level and reset to 0 then above.

then in E1 I added =MAX() for that column. 

[#3]

Holy moly. That's great. I needed three columns to process the data using my alternative method. Thankyou so so much for your time and help.

[#4]

Thanks Joe for your additional review and commentary. I'm most grateful for your input and help. The questions you raise are also helpful.
Thanks you to all responders.
I've resolved this query.