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 :)