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