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?

Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov
75%	92%	92%	75%	76%	85%	76%	97%	89%	70%	83%

<tbody>
</tbody><colgroup><col span="11"></colgroup>

 

[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!