Help with Counting Current streaks

B

bramdana

New Member
Joined
Jun 26, 2014
Messages
5
Morning All,

Having fun with this one. I have found almost the answer how as the sheet grows I can see the formula becoming inefficient and will need condensing.

I am making a table of my employees and want to reward those that consistently hit 100% on a certain area.

See Below the table


Current Weeks @ 100%Current FormulaWeek 12Week 11Week 10Week 9Week 8Week 7Week 6Week 5Week 4Week 3Week 2Week 1
John3310010010099100100100100100100100100
Dan1212100100100100100100100100100100100100
Paul0080100100100100100100100100100100100
Ed32100100
1009010040100100100100100
Brian1212100100100100100100100100100100100100
Jason6610010010010010010086100100100100100
Ian007090100100100100100100100100100100
Chris50
1001001001001001310100100100100

<tbody>
</tbody>


The Column "Current Weeks at 100%" has been done manually

I need to count how many times an employee has hit 100% consecutively over a period of weeks. - I have done this using the below formula: - Seen in a ction in the "Current Formula" coloumn

Code: 
=IF(D3=100,IF(E3=100,IF(F3=100,IF(G3=100,IF(H3=100,IF(I3=100,IF(J3=100,IF(K3=100,IF(L3=100,IF(M3=100,IF(N3=100,IF(O3=100,"12","11"),"10"),"9"),"8"),"7"),"6"),"5"),"4"),"3"),"2"),"1"),"0")

The problem is:

A. I have 100 employees so the amount of calculation is going to get big.
B. This is only upto 12 weeks, Obviously I want to build this as on going so for at least 52 Weeks.
C. My Formula does not take into account 0's or blanks (Holidays).

Please can someone help. I have looked at the Frequency usage but cannot seem to get only consecutive numbers.

All help is appreciated.
 
Sort by date Sort by votes
Hey I am not sure but does this:

Code: 
=MAX(FREQUENCY(IF((D2:P2=100),COLUMN(D2:P2)),IF((100<>D2:P2)*(""<>D2:P2),COLUMN(D2:P2))))

solve the problem of finding the highest streak even with the blanks included???
 
Upvote 0

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
This formula in column B should do it:
Code: 
=IF(SUM(D2:O2)=(COLUMN($O$1)-(COLUMN($D$1)-1))*100,COUNT(D2:O2),MIN(IF((D2:O2<100)*(COLUMN(D2:O2))<>0,COLUMN(D2:O2)))-COLUMN($D$1))
It's an array so use Ctrl+Shift+Enter
 
Upvote 0
Paella1 said:
This formula in column B should do it:
Code: 
=IF(SUM(D2:O2)=(COLUMN($O$1)-(COLUMN($D$1)-1))*100,COUNT(D2:O2),MIN(IF((D2:O2<100)*(COLUMN(D2:O2))<>0,COLUMN(D2:O2)))-COLUMN($D$1))
It's an array so use Ctrl+Shift+Enter
Click to expand...

Doesn't appear to work with the blanks!
 
Upvote 0
It does for me. I'm assuming a blank IS NOT equal to 100.

Jubledore's solution seems to be counting up from Week 1, instead of back from the last week.
 
Upvote 0
Paella1 said:
It does for me. I'm assuming a blank IS NOT equal to 100.

Jubledore's solution seems to be counting up from Week 1, instead of back from the last week.
Click to expand...

If there is a blank the OP said it should count 100's before and after it.

My formula results with yours below

Code: 
[TABLE="width: 837"]
<colgroup><col><col><col><col><col span="2"><col span="3"><col span="9"></colgroup><tbody>[TR]
[TD][/TD]
[TD]Current Streak[/TD]
[TD]Best Streak[/TD]
[TD][/TD]
[TD]Week 14[/TD]
[TD]Week 13[/TD]
[TD]Week 12[/TD]
[TD]Week 11[/TD]
[TD]Week 10[/TD]
[TD]Week 9[/TD]
[TD]Week 8[/TD]
[TD]Week 7[/TD]
[TD]Week 6[/TD]
[TD]Week 5[/TD]
[TD]Week 4[/TD]
[TD]Week 3[/TD]
[TD]Week 2[/TD]
[TD]Week 1[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]5[/TD]
[TD]8[/TD]
[TD][/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]99[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD]Dan[/TD]
[TD]13[/TD]
[TD]13[/TD]
[TD][/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD][/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD]Paul[/TD]
[TD]12[/TD]
[TD]12[/TD]
[TD][/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD][/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD][/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD]Ed[/TD]
[TD]5[/TD]
[TD]7[/TD]
[TD][/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD][/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD]Brian[/TD]
[TD]1[/TD]
[TD]12[/TD]
[TD][/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD]Jason[/TD]
[TD]7[/TD]
[TD]7[/TD]
[TD][/TD]
[TD="align: right"]100[/TD]
[TD][/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]86[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD]Ian[/TD]
[TD]1[/TD]
[TD]10[/TD]
[TD][/TD]
[TD="align: right"]100[/TD]
[TD][/TD]
[TD="align: right"]70[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD]Chris[/TD]
[TD]7[/TD]
[TD]7[/TD]
[TD][/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD][/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Paella1 said:
It does for me. I'm assuming a blank IS NOT equal to 100.

Jubledore's solution seems to be counting up from Week 1, instead of back from the last week.
Click to expand...

I think the formula you have mentioned is to get the last running streak not the greatest streak but even so it doesn't work if there are blanks. For example:

100, 100, [blank cell], 100 should give the last running streak as 3 but your formula gives 2
 
Upvote 0
Paella1 said:
It does for me. I'm assuming a blank IS NOT equal to 100.

Jubledore's solution seems to be counting up from Week 1, instead of back from the last week.
Click to expand...

Jumbledore's formula gives the BEST streak, not the current streak.
 
Upvote 0
gaz_chops said:
Jumbledore's formula gives the BEST streak, not the current streak.
Click to expand...

actually it wasn't my formula but somebody else's. I just modified the formula to get a more accurate result. But anyway it is such a fantastic formula... so much you can do with it!
 
Upvote 0
You must log in or register to reply here.

Similar threads

Cubist
  • Question
Pivot Table with Overlapping Row
Replies
0
Views
114
Cubist
Cubist
A
  • Solved
Problem with multiple IFS, IF and AND in long formula
Replies
2
Views
172
arendberg
A
JenniferMurphy
  • Solved
Is there a better way to format table headers?
Replies
9
Views
185
shift-del
shift-del
G
  • Question
Weighted scores with some weightings the full 100%
Replies
7
Views
100
etaf
etaf
L
  • Solved
please help! my brain isnt brain-ing today
2
Replies
10
Views
197
LIZEEY
L

Forum statistics

Threads
1,215,475
Messages
6,125,028
Members
449,205
Latest member
Eggy66

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top