Looking for the longest run of zeros in a column

[Marvo]

Hi All, newbie here. I was perusing your excellent site looking for an answer to a problem I have. I have two columns (K & L) with over 5000 rows with data. I'm interested in finding the longest string of zeros. (then afterwards adding extra criteria (Column E with either "H" or "A" in it & Column I with "League") I'm stumped at the minute even without adding the criteria. However I came across this thread on here

Finding longest sequence of the same value in a row

Hi all, Hope someone can please help with this as I've spent hours scratching my head. :) I'm looking for a formula that calculates the green values in the J column (which I've added in manually). I need the formula to please find the longest streak in the row of consecutive instances of the...

www.mrexcel.com

and I think its very similar to what I need, though its a column, not a row and I'm only interested in zeros.

Many thanks.

 

[Marvo]

Sorry, should have said I have been searching for the answer to this elsewhere.

Seaching for longest string without a zero

Hi All, In the attached workbook, on worksheet "Records", I'm struggling with the formula in cells O4 and O5. In O4 its looking at Column K on worksheet "All" for the longest string of games without a zero. The answer is 18 but however I try and restructure the formula I can't get that...

www.excelforum.com

 

[RoryA]

Perhaps:

=MAX(FREQUENCY(IF(ALL!$L$2:$L$9999&""="0",ROW(ALL!$L$2:$L$9999)),IF(ALL!$L$2:$L$9999<>0,ROW(ALL!$L$2:$L$9999))))

?

Also, I think you can avoid the INDIRECT problems by amending your other summary formulas to something like:

=MAX(FREQUENCY(IF((ALL!$E$2:$E$9999="H")*(ALL!K2:K9999<>0),ROW(ALL!K2:K9999)),IF((ALL!$E$2:$E$9999="H")*(ALL!K2:K9999=0),ROW(ALL!K2:K9999))))

for Home for example.

 

[Marvo]

Thank You.

It returned, "A value in the formula is of the wrong data type"

 

[Marvo]

Opps. My mistake, it worked. I forgot it needed Control, shirt, enter.

Can I now add criteria to that?

 

[RoryA]

Can I now add criteria to that?

Yes - it will be similar syntax to the second formula I posted.

 

[Marvo]

Wow. That's fantastic, you've been a great help.

 

[RoryA]

Glad we could help, and a belated welcome to the forum!