Calculating time water level below threshold of 2.7m per event

%3CLINGO-SUB%20id%3D%22lingo-sub-2819967%22%20slang%3D%22en-US%22%3ECalculating%20time%20water%20level%20below%20threshold%20of%202.7m%20per%20event%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2819967%22%20slang%3D%22en-US%22%3E%3CP%3EHi%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20to%20calculate%20the%20total%20time%20that%20a%20water%20level%20is%20below%20the%20threshold%20of%202.7m.%20I%20need%20to%20do%20this%20for%20each%20event%20where%20this%20occurs.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20trying%20to%20work%20out%20the%20longest%20period%20where%20water%20is%20below%20the%202.7m%20threshold.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAt%20the%20moment%20I%20am%20looking%20at%20a%20manual%20fix%20but%20know%20there%20is%20an%20intelligent%20way%20to%20solve%20this%20issue.%20Can%20you%20help%20me%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2819967%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2820316%22%20slang%3D%22en-US%22%3ERe%3A%20Calculating%20time%20water%20level%20below%20threshold%20of%202.7m%20per%20event%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2820316%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1177718%22%20target%3D%22_blank%22%3E%40Bella_75%3C%2FA%3E%26nbsp%3BAs%20with%20many%20things%20Excel%20there%20are%20more%20than%201%20ways%20to%20skin%20the%20cat.%26nbsp%3B%20Here%20is%201%20that%20I%20think%20is%20pretty%20'simple'.%26nbsp%3B%20I%20added%20this%20equation%20to%20the%20column%20next%20to%20the%20depth%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DIF(D2%26lt%3B2.7%2CC2-C1%2BE1%2C0)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EIt%20will%20simply%20add%20the%20latest%20time%20span%20to%20the%20previous%20if%20the%20depth%20is%20below%20the%20level%20and%20reset%20to%200%20then%20above.%3C%2FP%3E%3CP%3Ethen%20in%20E1%20I%20added%20%3DMAX()%20for%20that%20column.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2820420%22%20slang%3D%22en-US%22%3ERe%3A%20Calculating%20time%20water%20level%20below%20threshold%20of%202.7m%20per%20event%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2820420%22%20slang%3D%22en-US%22%3EHoly%20moly.%20That's%20great.%20I%20needed%20three%20columns%20to%20process%20the%20data%20using%20my%20alternative%20method.%20Thankyou%20so%20so%20much%20for%20your%20time%20and%20help.%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2820886%22%20slang%3D%22en-US%22%3ERe%3A%20Calculating%20time%20water%20level%20below%20threshold%20of%202.7m%20per%20event%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2820886%22%20slang%3D%22en-US%22%3EThanks%20Joe%20for%20your%20additional%20review%20and%20commentary.%20I'm%20most%20grateful%20for%20your%20input%20and%20help.%20The%20questions%20you%20raise%20are%20also%20helpful.%3CBR%20%2F%3EThanks%20you%20to%20all%20responders.%3CBR%20%2F%3EI've%20resolved%20this%20query.%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2854684%22%20slang%3D%22en-US%22%3ERe%3A%20Calculating%20time%20water%20level%20below%20threshold%20of%202.7m%20per%20event%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2854684%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1177718%22%20target%3D%22_blank%22%3E%40Bella_75%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20might%20consider%20adding%20the%20area%20to%20your%20criteria%3C%2FP%3E%3CP%3Esince%20the%20area%20below%202.7m%20is%20quite%20irregular.%3C%2FP%3E%3CP%3EPlease%20review%20my%20chart.%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fwww.mediafire.com%2Ffile%2Fpcjti99pl4gkf79%2F10_06_21c.xlsx%2Ffile%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.mediafire.com%2Ffile%2Fpcjti99pl4gkf79%2F10_06_21c.xlsx%2Ffile%3C%2FA%3E%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fwww.mediafire.com%2Ffile%2F54zqsfg78y7onve%2F10_06_21c.pdf%2Ffile%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.mediafire.com%2Ffile%2F54zqsfg78y7onve%2F10_06_21c.pdf%2Ffile%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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.

4 Replies

@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. 

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.
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.

@Bella_75 

You might consider adding the area to your criteria

since the area below 2.7m is quite irregular.

Please review my chart.

https://www.mediafire.com/file/pcjti99pl4gkf79/10_06_21c.xlsx/file

https://www.mediafire.com/file/54zqsfg78y7onve/10_06_21c.pdf/file