Count Longest Streak

[mrjnice85]

Hey everyone,

I have an excel file that I would like to capture the longest streak of cells that contain a number greater than 0. I currently have an array formula in cell J14:

{=MAX(FREQUENCY(IF(L14:AM14<>$BI$83,COLUMN(L14:AM14)), IF(L14:AM14=$BI$83,COLUMN(L14:AM14))))} ($BI$83 is just an empty cell)

The problem I run into is that all the cells in that range of L14:AM14 are formulas. So my array can't use >0 or =0. The problem with that existing array is it includes cells which the value of the formula is "x" or "ooc". I'm not overly confident with array's and I pieced that formula together through luck of searching around but haven't found out how to omit the "x" and "occ". Any help would be greatly appreciated. Here is an example of the table below:

[TABLE="class: grid, width: 800"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]J[/TD]
[TD="align: center"]K[/TD]
[TD="align: center"]L[/TD]
[TD="align: center"]M[/TD]
[TD="align: center"]N[/TD]
[TD="align: center"]O[/TD]
[TD="align: center"]P[/TD]
[TD="align: center"]Q[/TD]
[TD="align: center"]R[/TD]
[TD="align: center"]S[/TD]
[TD="align: center"]T[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]Username[/TD]
[TD="align: center"]Longest Streak[/TD]
[TD="align: center"][/TD]
[TD="align: center"]944[/TD]
[TD="align: center"]943[/TD]
[TD="align: center"]942[/TD]
[TD="align: center"]941[/TD]
[TD="align: center"]940[/TD]
[TD="align: center"]939[/TD]
[TD="align: center"]938[/TD]
[TD="align: center"]937[/TD]
[TD="align: center"]936[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]---[/TD]
[TD="align: center"]---[/TD]
[TD="align: center"]---[/TD]
[TD="align: center"]---[/TD]
[TD="align: center"]---[/TD]
[TD="align: center"]---[/TD]
[TD="align: center"]---[/TD]
[TD="align: center"]---[/TD]
[TD="align: center"]---[/TD]
[TD="align: center"]---[/TD]
[TD="align: center"]---[/TD]
[TD="align: center"]---[/TD]
[/TR]
[TR]
[TD="align: center"]14[/TD]
[TD="align: center"]Eliboyi[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"][/TD]
[TD="align: center"]OOC[/TD]
[TD="align: center"]x[/TD]
[TD="align: center"]12324[/TD]
[TD="align: center"]12452[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]9708[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]8000[/TD]
[/TR]
[TR]
[TD="align: center"]21[/TD]
[TD="align: center"]Gibsy[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"][/TD]
[TD="align: center"]15536[/TD]
[TD="align: center"]11996[/TD]
[TD="align: center"]12404[/TD]
[TD="align: center"]9352[/TD]
[TD="align: center"]9360[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]9256[/TD]
[TD="align: center"]12468[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

As you can see for J14 it returns a streak of 4 when it should only be 2 since we don't want to include the OOC's or X

 

[Domenic]

Try...

=MAX(FREQUENCY(IF(ISNUMBER(L14:AM14),IF(L14:AM14>0,COLUMN(L14:AM14))),IF((1-ISNUMBER(L14:AM14))+(L14:AM14=0),COLUMN(L14:AM14))))

...confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

 

[mrjnice85]

That did the trick, thank you so much!

 

[Domenic]

You're very welcome!

Cheers!