Count of Consecutive Zeros excluding #N/A

This is my column of data:


7
8
9
7
5
7
6
9
11
6
7
2
6
2
4
1
1
0
1
3
1
0
0
2
1
2
0
1
3
1
1
0
1
0
0
0
0
0
0
#N/A
#N/A
#N/A
#N/A
#N/A

The data in this column is the result of a sumproduct function and returns an #N/A error so that dynamic graphing can occur after input of primary data on another sheet.

The problem I am having is I wish to be able to show:

The Current Run of Zero events.
The longest run of zero events
The nth longest run of zero events (i.e. 2nd or 3rd etc.)

The formula I have in the next column is currently this:

=IF(OR(H3<>0,H3="#N/A"),"",MATCH(TRUE,H3:H$112<>0,0)-1)

I can then use a max formula of the new column to find the longest run.

The problem I have is that the latest run is of 7 consecutive 0s and it does not tell me this. It is resulting in an #N/A error because of the #N/A outputs in the first column. The longest run of Zeros I currently get from my max formula is 2.

I have ready other formulas using FREQUENCY etc and have tried to adapt them to my situation but seem to be failing spectacularly. Any help would be greatly appreciated :)

hey bro,
use this...

Formula:

Please Login or Register  to view this content.

Say thanks, click *

Thank you so much for your help!!

Originally Posted by Tony Valko

Will the #N/A errors always be at the bottom of the data?

Hi Tony,

Yes the data will always have the errors at the bottom as it reflects that data has not yet been entered on the primary data sheet yet so far this year.

This problem has been solved already though. I modified the formula given to me earlier by kvsrinivasamurthy & this is what it now looks like:

=IFERROR(IF(AND(H3<>0,H2=0),0,IFERROR(IF(AND(H3=0,H2<>0),MATCH(FALSE,IFERROR(H3:H$112,"")=0,-1),"")-1,"")),"")

in a helper column followed by:

=LARGE('Statistics (2)'!$H$3:$H$112,'Dashboard 2'!$D$7).

Try this:

No helper column is required.

=MAX(FREQUENCY(IF(ISNUMBER(A1:A44),IF(A1:A44=0,ROW(A1:A44))),IF(ISNUMBER(A1:A44),IF(A1:A44<>0,ROW(A1:A44)))))

Array formula: Press Ctrl+Shift+Enter, not just Enter