I am trying to count the number of values in preceding cells which are greater than a certain value (in this case 19), but ignoring zero values.
Put another way, I want to find the number of consecutive weeks that a staff member has reached target, but excluding holidays.
I have googled this to death but cannot find a way to do it.
Any help greatly appreciated.
Example below:
<tbody>
</tbody>
It is the final column I am seeking a formula for. I have entered the expected result.
Put another way, I want to find the number of consecutive weeks that a staff member has reached target, but excluding holidays.
I have googled this to death but cannot find a way to do it.
Any help greatly appreciated.
Example below:
week 1 | week 2 | week 3 | week 4 | week 5 | week 6 | consecutive weeks >=19 | |
Cheryl Wood | 28.6 | 0 | 0 | 28.5 | 27.9 | 31.5 | 4 |
Roger Ward | 10.4 | 22.8 | 27.7 | 11.9 | 0 | 16.9 | 0 |
Nancy Butler | 12.7 | 23.9 | 14.3 | 18.2 | 24.8 | 22.7 | 2 |
Angela Simmons | 0 | 27.6 | 23.4 | 21.9 | 20.4 | 0 | 4 |
Martha Perry | 11.7 | 13.9 | 0 | 0 | 15.7 | 28.2 | 1 |
Lawrence Lewis | 23.6 | 0 | 0 | 19.9 | 18.4 | 20.1 | 1 |
<tbody>
</tbody>
It is the final column I am seeking a formula for. I have entered the expected result.