Counting Consecutive Zeroes

[ststern45]

Hi everyone,

I have this formula that will count how many consecutive zeroes appear at the bottom of a range:

=MAX((U20:U219<>"")*(ROW(U20:U219)))-MAX((U20:U219<>0)*(U20:U219<>"")*(ROW(U20:U219)))

How do I change the formula where it counts from the beginning of a range. For example,

U20 = 0
U21 = 0
U22 = 0
U23 = 1 (Note: can be any number other than 0 (zero)

So the correct calculation would be 3 (3 consecutive zeroes)

Thanks in advance!!

 

[Peatawn]

Hi ststern,

I think I might be able to help. I see in your example that your three consecutive 0's are for U20 to U22. What is your counting plan if for example you have:

U20 = 0
U21 = 0
U22 = 0
U23 = 1
U24 = 0
U25 = 0
U26 = 7

We have two sets of consecutive 0's. A set of 3 and a set of 2. Also, can you specify the range: is it fixed, variable, where it begins (A1 or somewhere else)...

Regards,
Peatawn

 

[ststern45]

If U20 is other than a zero the result would be 0. I just want to count how many consecutive zeroes begin at cell U20 going downward. One it reaches any value other than a 0 it would stop. So in you example the result would be 3 since there are 3 consecutive zeroes starting a cell U20

 

[Peatawn]

Is there a max range, say U60 (for example) or could it be indefinite?

 

[ststern45]

The range is always 20:219 for example U20:U219, V20:V219, etc.

Here is another formula I found that calculated from the bottom:

=COUNTIF(INDEX(U20:U219,IF(COUNTIF(U20:U219,">0"),MATCH(2,1/(U20:U219>0)),1)):INDEX(U20:U219,MATCH(2,1/(U20:U219<>""))),0)

 

[Peatawn]

I just tried this on my end and works perfectly. Not that I've tested hundreds of combinations, but you might want to try this:

=MATCH(NOT(0)+0,U21:U219,0)-1

Let me know if it works!

 

[Peatawn]

Hmm, forget that, I just tried more combinations and it didn't work for all.

 

[Peatawn]

Quite a challenge. I love that. Is there a maximum number thatcould break the sequence of 0's. I mean, could it be, 0, 0, 0, 102579... or is there a max value for the number that breaks the sequence?

 

[L. Howard]

In V20 enter first formula. In V21 enter second formula and pull down.

=IF(U20=0,1,0)

=IF(U21=0,1+V20,0)

Not sure if this is the kind of read out you want or can live with.

Regards,
Howard

 

[ststern45]

The calculation is based on how many consecutive zeroes appear starting on line 20 through 219.

Link to image:

http://farm9.staticflickr.com/8523/8676818453_c25f98450a.jpg