counting number and duration of non-continuous events

Hello

I'm hoping someone out there can help! I am trying to add up the number of times an event occurs over a set time period. For background, the events in question are the number of days that river flow has dropped below a particular level (the compliance flow, denoted by negative numbers in column B which I have simplified into either a 0- flow above compliance flow threshold or 1- flow below compliance).

What I am particularly interested in is how many times flow is non compliant, or is denoted by a '1' (NOT the total number of days flow is non-compliant, but the number of events which occur, so if flow is non-compliant for 1 day- that is an event, as is a time when flow is non-compliant for 6 days; together those just count as 2 events). In addition to that, I would like to know the average length (days) the events are, over either 6,3 or 1 months as well as the longest period of non-compliance (in days). Basically, what I am trying to figure out is how long and how often low flow events occur.

I've been tinkering with this for ages, but can't even get a handle on how to handle non-continuous events, so any help is very much appreciated!


Thank you!

I have attached a file to illustrate how the data is currently arranged.

Try these. All of these formulas are array formulas**.

Enter the date range of interest in some cells. For these examples we'll just get the calculations for the month of Jan 1990.

E3 = 1/1/1990
F3 = 1/31/1990

For the # non compliance events:

=SUM(IF(FREQUENCY(IF(A2:A213>=E3,IF(A2:A213<=F3,IF(C2:C213=1,ROW(A2:A213)))),IF(A2:A213>=E3,IF(A2:A213<=F3,IF(C2:C213<>1,ROW(A2:A213))))),1))

For the average length of event:

=AVERAGE(IF(FREQUENCY(IF(A2:A213>=E3,IF(A2:A213<=F3,IF(C2:C213=1,ROW(A2:A213)))),IF(A2:A213>=E3,IF(A2:A213<=F3,IF(C2:C213<>1,ROW(A2:A213))))),FREQUENCY(IF(A2:A213>=E3,IF(A2:A213<=F3,IF(C2:C213=1,ROW(A2:A213)))),IF(A2:A213>=E3,IF(A2:A213<=F3,IF(C2:C213<>1,ROW(A2:A213)))))))

For the max duration of non compliance:

=MAX(FREQUENCY(IF(A2:A213>=E3,IF(A2:A213<=F3,IF(C2:C213=1,ROW(A2:A213)))),IF(A2:A213>=E3,IF(A2:A213<=F3,IF(C2:C213<>1,ROW(A2:A213))))))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

You're welcome. Thanks for the feedback!