Formula to count continuous instances

[pdchislett]

Hi,

I have a spreadsheet containing data for 52 weeks.

So something like.. (in columns A & B)

Wk1 1000
Wk2 2000
Wk3 0
Wk4 0
Wk5 0
Wk6 1000
Wk7 0
Wk8 0
Wk9 3000


Is there a formula I can use to count the maximum number of continuous zeroes? So in the above the answer would be 3 (the range between wk3-5)

Thanks

 

[Domenic]

Assuming that A2:B10 contains the data, try...

=MAX(FREQUENCY(IF(B2:B10=0,ROW(B2:B10)),IF(B2:B10<>0,ROW(B2:B10))))

Note, however, if the data contains empty cells and you'd like them to be ignored, the formula will need to be amended.

 

[pdchislett]

Hi Domenic,

I manipulated the formula (to the correct range in my spreadsheet) and got a #VALUE!.

I also replicated the example i gave and still got the same result?

There arent any blank cells in the data..

Thanks

 

[Domenic]

Sorry, the formula needs to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER. If done correctly, Excel will automatically place curly braces {...} around the formula.

 

[pdchislett]

Excellent - Thanks

 

[Domenic]

You're very welcome!

 

[scorpion_king84]

You're very welcome!

Hi Domenic, I have almost same problem, here is my query:

5.5
8
7.5
6
5.5
7.5
4.5

<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>
</tbody>

This data ranged from A1:A18. In between there are blank cells but contained formula. I want to calculate how many times this series has more equal to or more than 5 consecutive entries by formula. Such as in this case there is only one range from A7:A11 (5 consecutive numbers).

 

[Domenic]

Try...

=SUM(IF(FREQUENCY(IF(ISNUMBER(A1:A18),ROW(A1:A18)),IF(1-ISNUMBER(A1:A18),ROW(A1:A18)))>=5,1))

...confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

 

[scorpion_king84]

Thanks Domenic, it worked!

suppose A1:A18, every cell in this range has some value then answer should come 3 because data has 5 consecutive values three times or
in other scenario range from A1:A6 has values and then A7 blank having formula in it and from A8:A14 has values, then answer should come 2 because there are twice 5 consecutive values.

 

[Domenic]

You're very welcome. I'm on my way out, though, so I would suggest that you start a new thread and ask you question there.