Counting Consecutives

[InzieBear]

I have a question re formula.

I have a workbook that contains the % of accuracy for each entity.... I.e. Jan to Dec (75%,92%...).

What I want to do is to count the consecutive occurrences where they have failed to meet a target (i.e. 95%) but if they meet the target its reset to zero until the following month where the consecutive would be 1 if they missed.

In the below example there would only be 3 consecutive occurrences (Sep-Nov.) as Aug was 97% so above target so reset the count back to zero. However if I was calculating for Jan - Jul it would have been 7 until August month.

Hope this makes sense....anyone help?
[TABLE="width: 704"]
<tbody>[TR]
[TD]Jan[/TD]
[TD]Feb[/TD]
[TD]Mar[/TD]
[TD]Apr[/TD]
[TD]May[/TD]
[TD]Jun[/TD]
[TD]Jul[/TD]
[TD]Aug[/TD]
[TD]Sep[/TD]
[TD]Oct[/TD]
[TD]Nov[/TD]
[/TR]
[TR]
[TD="align: right"]75%[/TD]
[TD="align: right"]92%[/TD]
[TD="align: right"]92%[/TD]
[TD="align: right"]75%[/TD]
[TD="align: right"]76%[/TD]
[TD="align: right"]85%[/TD]
[TD="align: right"]76%[/TD]
[TD="align: right"]97%[/TD]
[TD="align: right"]89%[/TD]
[TD="align: right"]70%[/TD]
[TD="align: right"]83%
[/TD]
[/TR]
</tbody><colgroup><col span="11"></colgroup>[/TABLE]

 

[InzieBear]

In regards to the custom function. This works to count all % higher or lower than 0.95 but once the target reach the >0.95 it should technically go back to ZERO.

 

[InzieBear]

I have managed to get the -54 to stop by changing the formula slights (Still an array).

=IF(MAX(IF(ISNUMBER(AR9:BC9),COLUMN(AR9:BC9)),1)-MAX(IF(AR9:BC9>=95%,COLUMN(AR9:BC9)),1)=-54,0,MAX(IF(ISNUMBER(AR9:BC9),COLUMN(AR9:BC9)),1)-MAX(IF(AR9:BC9>=95%,COLUMN(AR9:BC9)),1))

 

[Eric W]

I am puzzled, I haven't been able to duplicate your -54 error. If you want me to take another look, then try to post your sample data. It may be due to the particular rows/columns/way you enter the data that's different from my test sheet.

But if you're happy with your revised version, that's fine too!