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]
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]