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]

 

[Eric W]

Counting consecutive occurrences can be done, but I'm a little unsure about what you want. Do you want a running total of the occurrence? If so, try:

	A	B	C	D	E	F	G	H	I	J	K
Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[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]

[TD="align: center"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]

</tbody>

Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]A3[/TH]
[TD="align: left"]=IF(A2<95%,1,0)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B3[/TH]
[TD="align: left"]=IF(B2<95%,A3+1,0)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



Put the A3 formula in. Then put the B3 formula in and drag to the right.

Or if you want to find the longest run, this could work:

=MAX(FREQUENCY(IF(A2:K2<95%,COLUMN(A2:K2)),IF(A2:K2>=95%,COLUMN(A2:K2))))
confirmed with Control+Shift+Enter.

Let us know if this works, or if you need something else.

 

[InzieBear]

Hey Eric

Thanks for trying to help.

Im trying to do something similar to the first part you described. However, I only want it to show the current month. So (M1) would say "Consecutive Target Miss" then it would only calculate based on the month. Ie if its july it would only do until July. - hope that makes sense.
I did think the If statement it just makes it very long if your only doing it in one coloumn.

 

[Eric W]

If you determine the current month just by which cells have data in them, then maybe:

Excel 2012

	A	B	C	D	E	F	G	H	I	J	K	L	M

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FFFFFF"]Jan[/TD]
[TD="bgcolor: #FFFFFF"]Feb[/TD]
[TD="bgcolor: #FFFFFF"]Mar[/TD]
[TD="bgcolor: #FFFFFF"]Apr[/TD]
[TD="bgcolor: #FFFFFF"]May[/TD]
[TD="bgcolor: #FFFFFF"]Jun[/TD]
[TD="bgcolor: #FFFFFF"]Jul[/TD]
[TD="bgcolor: #FFFFFF"]Aug[/TD]
[TD="bgcolor: #FFFFFF"]Sep[/TD]
[TD="bgcolor: #FFFFFF"]Oct[/TD]
[TD="bgcolor: #FFFFFF"]Nov[/TD]
[TD="bgcolor: #FFFFFF"]Dec[/TD]
[TD="bgcolor: #FFFFFF"]Consecutive Target Miss[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FFFFFF, align: right"]75%[/TD]
[TD="bgcolor: #FFFFFF, align: right"]92%[/TD]
[TD="bgcolor: #FFFFFF, align: right"]92%[/TD]
[TD="bgcolor: #FFFFFF, align: right"]75%[/TD]
[TD="bgcolor: #FFFFFF, align: right"]76%[/TD]
[TD="bgcolor: #FFFFFF, align: right"]85%[/TD]
[TD="bgcolor: #FFFFFF, align: right"]76%[/TD]
[TD="bgcolor: #FFFFFF, align: right"]97%[/TD]
[TD="bgcolor: #FFFFFF, align: right"]89%[/TD]
[TD="bgcolor: #FFFFFF, align: right"]70%[/TD]
[TD="bgcolor: #FFFFFF, align: right"]83%[/TD]
[TD="align: right"][/TD]
[TD="align: right"]3[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: #DAE7F5"]M2[/TH]
[TD="align: left"]{=MAX(IF(ISNUMBER(A2:L2),COLUMN(A2:L2)))-MAX(IF(A2:L2>=95%,COLUMN(A2:L2)))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

 

[InzieBear]

Hey Eric...this might help a little more. ( im new to this forum so still discovering some things )
I need to get the column Consecutive to count the number of consecutive times an entity has missed its target (95%). Once it reach 95% or above, the consecutive goes back to zero...

I have manually done the below to show what I need....can anyone help? In my spreadsheet I have conditional formatting in the Jan-Dec part to show Green for 99%+, Amber for 95%+ and Red for <95% (not sure if this would impact the formula).

[TABLE="width: 500"]
<tbody>[TR]
[TD]

Name[/TD]
[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]
[TD]Dec[/TD]
[TD]Consecutive[/TD]
[/TR]
[TR]
[TD]Entity 1[/TD]
[TD]63%[/TD]
[TD]97%[/TD]
[TD]89%[/TD]
[TD]92%[/TD]
[TD]63%[/TD]
[TD]64%[/TD]
[TD]90%[/TD]
[TD]92%[/TD]
[TD]56%[/TD]
[TD]77%[/TD]
[TD]97%[/TD]
[TD]100%[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Entity 2[/TD]
[TD]47%[/TD]
[TD]64%[/TD]
[TD]90%[/TD]
[TD]99%[/TD]
[TD]86%[/TD]
[TD]70%[/TD]
[TD]81%[/TD]
[TD]67%[/TD]
[TD]72%[/TD]
[TD]92%[/TD]
[TD]83%[/TD]
[TD]58%[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]Entity 3[/TD]
[TD]40%[/TD]
[TD]45%[/TD]
[TD]89%[/TD]
[TD]56%[/TD]
[TD]72%[/TD]
[TD]55%[/TD]
[TD]71%[/TD]
[TD]84%[/TD]
[TD]89%[/TD]
[TD]89%[/TD]
[TD]86%[/TD]
[TD]86%[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]Entity 4[/TD]
[TD]50%[/TD]
[TD]47%[/TD]
[TD]63%[/TD]
[TD]73%[/TD]
[TD]58%[/TD]
[TD]39%[/TD]
[TD]99%[/TD]
[TD]29%[/TD]
[TD]24%[/TD]
[TD]25%[/TD]
[TD]69%[/TD]
[TD]59%[/TD]
[TD]5[/TD]
[/TR]
</tbody>[/TABLE]

 

[InzieBear]

This is perfect! Thank you!
However, when I have zero in for Jan to Dec it counts as -55......?


Can I ask....is it possible to manipulate slightly?

Ie can I leave the formula to calculate Jan -Dec and say in April the value May-Dec would be blank...therefore would this still calculate correctly or would it pick up the blanks as being "on target" ?

 

[jscranton]

Actually this doesn't count consecutives. Let me tweak.

Here is custom function if you prefer:

Function missedTarget(r As Range) As Integer
Dim rng As Range
Dim cell As Range
Dim count As Integer


Set rng = r


count = 0


For Each cell In rng.Cells


    If cell.Value < 0.95 Then
        count = count + 1
    End If
Next


missedTarget = count


End Function

 

[Scott T]

Try this array formula, must be entered with Control-Shift-Enter =12-COUNTBLANK(A2:L2)-MAX(IF(A2:L2>=0.95,COLUMN(A2:L2),0))} copy down

 

[Eric W]

Try this one:

	A	B	C	D	E	F	G	H	I	J	K	L	M	N

<tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FAFAFA"]Name[/TD]
[TD="bgcolor: #FAFAFA"]Jan[/TD]
[TD="bgcolor: #FAFAFA"]Feb[/TD]
[TD="bgcolor: #FAFAFA"]Mar[/TD]
[TD="bgcolor: #FAFAFA"]Apr[/TD]
[TD="bgcolor: #FAFAFA"]May[/TD]
[TD="bgcolor: #FAFAFA"]Jun[/TD]
[TD="bgcolor: #FAFAFA"]Jul[/TD]
[TD="bgcolor: #FAFAFA"]Aug[/TD]
[TD="bgcolor: #FAFAFA"]Sep[/TD]
[TD="bgcolor: #FAFAFA"]Oct[/TD]
[TD="bgcolor: #FAFAFA"]Nov[/TD]
[TD="bgcolor: #FAFAFA"]Dec[/TD]
[TD="bgcolor: #FAFAFA"]Consecutive[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FAFAFA"]Entity 1[/TD]
[TD="bgcolor: #FAFAFA, align: right"]63%[/TD]
[TD="bgcolor: #FAFAFA, align: right"]97%[/TD]
[TD="bgcolor: #FAFAFA, align: right"]89%[/TD]
[TD="bgcolor: #FAFAFA, align: right"]92%[/TD]
[TD="bgcolor: #FAFAFA, align: right"]63%[/TD]
[TD="bgcolor: #FAFAFA, align: right"]64%[/TD]
[TD="bgcolor: #FAFAFA, align: right"]90%[/TD]
[TD="bgcolor: #FAFAFA, align: right"]92%[/TD]
[TD="bgcolor: #FAFAFA, align: right"]56%[/TD]
[TD="bgcolor: #FAFAFA, align: right"]77%[/TD]
[TD="bgcolor: #FAFAFA, align: right"] 97% [/TD]
[TD="bgcolor: #FAFAFA, align: right"]100%[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FAFAFA"]Entity 2[/TD]
[TD="bgcolor: #FAFAFA, align: right"]47%[/TD]
[TD="bgcolor: #FAFAFA, align: right"]64%[/TD]
[TD="bgcolor: #FAFAFA, align: right"]90%[/TD]
[TD="bgcolor: #FAFAFA, align: right"] 99% [/TD]
[TD="bgcolor: #FAFAFA, align: right"]86%[/TD]
[TD="bgcolor: #FAFAFA, align: right"]70%[/TD]
[TD="bgcolor: #FAFAFA, align: right"]81%[/TD]
[TD="bgcolor: #FAFAFA, align: right"]67%[/TD]
[TD="bgcolor: #FAFAFA, align: right"]72%[/TD]
[TD="bgcolor: #FAFAFA, align: right"]92%[/TD]
[TD="bgcolor: #FAFAFA, align: right"]83%[/TD]
[TD="bgcolor: #FAFAFA, align: right"]58%[/TD]
[TD="align: right"]8[/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #FAFAFA"]Entity 3[/TD]
[TD="bgcolor: #FAFAFA, align: right"]40%[/TD]
[TD="bgcolor: #FAFAFA, align: right"]45%[/TD]
[TD="bgcolor: #FAFAFA, align: right"]89%[/TD]
[TD="bgcolor: #FAFAFA, align: right"]56%[/TD]
[TD="bgcolor: #FAFAFA, align: right"]72%[/TD]
[TD="bgcolor: #FAFAFA, align: right"]55%[/TD]
[TD="bgcolor: #FAFAFA, align: right"]71%[/TD]
[TD="bgcolor: #FAFAFA, align: right"]84%[/TD]
[TD="bgcolor: #FAFAFA, align: right"]89%[/TD]
[TD="bgcolor: #FAFAFA, align: right"]89%[/TD]
[TD="bgcolor: #FAFAFA, align: right"]86%[/TD]
[TD="bgcolor: #FAFAFA, align: right"]86%[/TD]
[TD="align: right"]12[/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #FAFAFA"]Entity 4[/TD]
[TD="bgcolor: #FAFAFA, align: right"]50%[/TD]
[TD="bgcolor: #FAFAFA, align: right"]47%[/TD]
[TD="bgcolor: #FAFAFA, align: right"]63%[/TD]
[TD="bgcolor: #FAFAFA, align: right"]73%[/TD]
[TD="bgcolor: #FAFAFA, align: right"]58%[/TD]
[TD="bgcolor: #FAFAFA, align: right"]39%[/TD]
[TD="bgcolor: #FAFAFA, align: right"] 99% [/TD]
[TD="bgcolor: #FAFAFA, align: right"]29%[/TD]
[TD="bgcolor: #FAFAFA, align: right"]24%[/TD]
[TD="bgcolor: #FAFAFA, align: right"]25%[/TD]
[TD="bgcolor: #FAFAFA, align: right"]69%[/TD]
[TD="bgcolor: #FAFAFA, align: right"]59%[/TD]
[TD="align: right"]5[/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: #FAFAFA"]Entity 5[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: #FAFAFA"]Entity 6[/TD]
[TD="bgcolor: #FAFAFA, align: right"]47%[/TD]
[TD="bgcolor: #FAFAFA, align: right"]64%[/TD]
[TD="bgcolor: #FAFAFA, align: right"]90%[/TD]
[TD="bgcolor: #FAFAFA, align: right"] 99% [/TD]
[TD="bgcolor: #FAFAFA, align: right"]86%[/TD]
[TD="bgcolor: #FAFAFA, align: right"]70%[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: #FAFAFA"]Entity 7[/TD]
[TD="bgcolor: #FAFAFA, align: right"]40%[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]9[/TD]
[TD="bgcolor: #FAFAFA"]Entity 8[/TD]
[TD="bgcolor: #FAFAFA, align: right"]50%[/TD]
[TD="bgcolor: #FAFAFA, align: right"]47%[/TD]
[TD="bgcolor: #FAFAFA, align: right"]63%[/TD]
[TD="bgcolor: #FAFAFA, align: right"]73%[/TD]
[TD="bgcolor: #FAFAFA, align: right"]58%[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="align: right"]5[/TD]

[TD="align: center"]10[/TD]
[TD="bgcolor: #FAFAFA"]Entity 9[/TD]
[TD="bgcolor: #FAFAFA, align: right"]40%[/TD]
[TD="bgcolor: #FAFAFA, align: right"]45%[/TD]
[TD="bgcolor: #FAFAFA, align: right"]89%[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"]72%[/TD]
[TD="bgcolor: #FAFAFA, align: right"]55%[/TD]
[TD="bgcolor: #FAFAFA, align: right"]71%[/TD]
[TD="bgcolor: #FAFAFA, align: right"]84%[/TD]
[TD="bgcolor: #FAFAFA, align: right"]89%[/TD]
[TD="bgcolor: #FAFAFA, align: right"]89%[/TD]
[TD="bgcolor: #FAFAFA, align: right"]86%[/TD]
[TD="bgcolor: #FAFAFA, align: right"]86%[/TD]
[TD="align: right"]12[/TD]

</tbody>

Sheet9​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]N2[/TH]
[TD="align: left"]{=MAX(IF(ISNUMBER(B2:M2),COLUMN(B2:M2)),1)-MAX(IF(B2:M2>=95%,COLUMN(B2:M2)),1)}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



With Entity 9, and April is empty, that counts as a 0%. There should be a good mix of sample data there. Let me know how it works.

 

[InzieBear]

Hi Eric,

Thanks for your help. Apologies for the delay. I have been ill.

I have tried the new formula with the ",1" element and now I get -54 instead of -55. Everything else works fine. It just seems to be anything with either 0% or a blank that causes this error.

Any ideas?