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.
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
Sort by date Sort by votes
There is probably a better way! But this appears to work.

=IFERROR(IF(INDEX(D2:Q2,MATCH(99,D2:Q2,-1))=100,MATCH(99,D2:Q2,-1),MATCH(99,D2:Q2,-1)-1),0)-IFERROR(COUNTBLANK(INDIRECT("D"&ROW(A2)&":"&ADDRESS(ROW(A2),IF(INDEX(D2:Q2,MATCH(99,D2:Q2,-1))=100,MATCH(99,D2:Q2,-1),MATCH(99,D2:Q2,-1)-1)+3,4,1))),0)

I inserted a blank column between the Formula and week 12, this allows you to insert a new week.
Code: 
[TABLE="width: 863"]
<tbody>[TR]
[TD][/TD]
[TD]Current Weeks @ 100%[/TD]
[TD]Current Formula[/TD]
[TD][/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="align: right"]3[/TD]
[TD="align: right"]4[/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"]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="align: right"]12[/TD]
[TD="align: right"]0[/TD]
[TD][/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]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD]Paul[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]80[/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]Ed[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD][/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"]40[/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="align: right"]12[/TD]
[TD="align: right"]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="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="align: right"]6[/TD]
[TD="align: right"]6[/TD]
[TD][/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="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD][/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="align: right"]5[/TD]
[TD="align: right"]6[/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"]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]
</tbody>[/TABLE]

 
Upvote 0
Gaz

Thank you, this is exactly what was needed. I can adapt this to work upto the 52 Weeks then probably start a new document for the follwoing year.

Further question then. What would be the best way to determine the best streak that the person had achieved.

Example. Ian has achieved 0 weeks currently as a streak, however his best current streak is 10 weeks.

What do you think?
 
Upvote 0
You're welcome, remember to always insert the new week between the blank column and the last week.

Best streak is a tricky one, let me ponder on it!! Or see if someone else chips in.
 
Upvote 0
Does 3 for John mean the length of the last recent streak, that is, the streak of week 12, 11, and 10?
 
Last edited:
Upvote 0
Assuming you want the last streak of 100 counted, use this formula and enter it as an array formula: =MIN(FREQUENCY(IF(D2:O2=100,COLUMN(D2:O2)),IF(D2:O2<>100,COLUMN(D2:O2))))

To enter an array formula: don't just press ENTER but press CTRL+SHIFT+ENTER.

If you want to count the highest streak just change MIN at the strat of the formula into MAX.
 
Last edited:
Upvote 0
jorismoerings said:
Assuming you want the last streak of 100 counted, use this formula and enter it as an array formula: =MIN(FREQUENCY(IF(D2:O2=100,COLUMN(D2:O2)),IF(D2:O2<>100,COLUMN(D2:O2))))

To enter an array formula: don't just press ENTER but press CTRL+SHIFT+ENTER.

If you want to count the highest streak just change MIN at the strat of the formula into MAX.
Click to expand...

Max appears to work to give the highest streak, but min doesn't return the right answer for the current streak.
 
Upvote 0
You must log in or register to reply here.

Similar threads

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

Forum statistics

Threads
1,214,872
Messages
6,122,026
Members
449,061
Latest member
TheRealJoaquin

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