Excel: Help with Counting Current streaks


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 Formula Week 12 Week 11 Week 10 Week 9 Week 8 Week 7 Week 6 Week 5 Week 4 Week 3 Week 2 Week 1
John 3 3 100 100 100 99 100 100 100 100 100 100 100 100
Dan 12 12 100 100 100 100 100 100 100 100 100 100 100 100
Paul 0 0 80 100 100 100 100 100 100 100 100 100 100 100
Ed 3 2 100 100 100 90 100 40 100 100 100 100 100
Brian 12 12 100 100 100 100 100 100 100 100 100 100 100 100
Jason 6 6 100 100 100 100 100 100 86 100 100 100 100 100
Ian 0 0 70 90 100 100 100 100 100 100 100 100 100 100
Chris 5 0 100 100 100 100 100 13 10 100 100 100 100
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
=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.


This question generated 12 answers. To proceed to the answers, click here.

This thread is current as of June 28, 2015.


For more resources for Microsoft Excel