How to find average number of continuous days of zero flow

[ahes]

Hi there,

I'm trying to figure out what formula I could use to calculate the average number of continuous days with zero flow per month.

Example data:
In column G I have the date and in column H I have the rate of flow:

11/1/2001 0
11/2/2001 0
11/3/2001 0
11/4/2001 0
11/5/2001 0
11/6/2001 0
11/7/2001 0
11/8/2001 0
11/9/2001 0
11/10/2001 0
11/11/2001 0
11/12/2001 0
11/13/2001 0
11/14/2001 15.117
11/15/2001 36.715
11/16/2001 36.759
11/17/2001 19.097
11/18/2001 0
11/19/2001 9.983
11/20/2001 36.581
11/21/2001 36.717
11/22/2001 36.787
11/23/2001 20.243
11/24/2001 0
11/25/2001 0
11/26/2001 0
11/27/2001 0
11/28/2001 0
11/29/2001 0
11/30/2001 0

My boss wants to know what the average number of continuous days with 0 flow each month (from 1987 to 2016). In the above example for November 2001, there are three periods of zero flow, for which the durations are : 13 days, 1 day and 7 days. Average duration would be 7.

Is there a formula to help me figure this out?

[tim201110]

=AVERAGE(IF(LARGE(FREQUENCY(IF($B$1:$B$30=0,ROW($A$1:$A$30)),($B$1:$B$30<>0)*ROW($A$1:$A$30)),ROW(1:30))>0,LARGE(FREQUENCY(IF($B$1:$B$30=0,ROW($A$1:$A$30)),($B$1:$B$30<>0)*ROW($A$1:$A$30)),ROW(1:30)))) as array formula, in B:B there is a flow

[Raphaelp]

See also: https://www.mrexcel.com/forum/excel-...ml#post4760139