Determining longest 'run' of zero values

Hi all,

How would I get Excel to determine the longest 'run' of a given number in a list of numbers? E.g. suppose I have the following data in column A:

24
34
0
12
0
0
67
54
0
0
0
99

Now suppose I want Excel to look at that data and tell me what the longest consecutive run of zero's are. In this case it would be 3 zero's. But how does I do this?

Thanks
-Rob

Hi Domenic,

Yes that seems to work, although I'm not entirely sure what it actually does!

Thanks
-Rob

First, the reference to cell A13 should have been absolute. Therefore, the formula should be...

=IF(A1=0,MATCH(TRUE,A1:$A$13<>0,0)-1,"")

I've edited my previous post accordingly.

As for how the formula works...

For the first two cells, the formula returns an empty string since the corresponding value in Column A does not equal 0, as per the IF function.

For the third cell, the formula changes to the following...

=IF(A3=0,MATCH(TRUE,A3:$A$13<>0,0)-1,"")

Notice that the range for the MATCH function changes. Here's how it breaks down...

A3:$A$13<>0 returns the following array of values...

FALSE
TRUE
FALSE
FALSE
TRUE
TRUE
FALSE
FALSE
FALSE
TRUE
TRUE

MATCH(TRUE,A3:$A$13<>0,0) returns 2, since TRUE is the second value in the array

and

MATCH(TRUE,A3:$A$13<>0,0)-1 returns 1

For the fourth cell, the formula returns an empty string, since the corresponding value in Column A does not equal 0, as per the IF function.

For the fifth cell, the formula changes to the following...

=IF(A5=0,MATCH(TRUE,A5:$A$13<>0,0)-1,"")

Again, the range for the MATCH function changes. Now...

A5:$A$13<>0 returns the following array of values...

FALSE
FALSE
TRUE
TRUE
FALSE
FALSE
FALSE
TRUE
TRUE

Therefore...

MATCH(TRUE,A5:$A$13<>0,0) returns 3, since TRUE is the third value in the array

and

MATCH(TRUE,A5:$A$13<>0,0)-1 returns 2

This process continues in the same manner until the end. Then the MAX function provides the desired result.

Hope this helps!