Find consecutive cells above a certain value, ignoring zeroes.

[myredroom]

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:

[TABLE="class: grid, width: 574"]
<tbody>[TR]
[TD][/TD]
[TD]week 1[/TD]
[TD]week 2[/TD]
[TD]week 3[/TD]
[TD]week 4[/TD]
[TD]week 5[/TD]
[TD]week 6[/TD]
[TD]consecutive weeks >=19[/TD]
[/TR]
[TR]
[TD]Cheryl Wood[/TD]
[TD="align: right"]28.6[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]28.5[/TD]
[TD="align: right"]27.9[/TD]
[TD="align: right"]31.5[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]Roger Ward[/TD]
[TD="align: right"]10.4[/TD]
[TD="align: right"]22.8[/TD]
[TD="align: right"]27.7[/TD]
[TD="align: right"]11.9[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]16.9[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Nancy Butler[/TD]
[TD="align: right"]12.7[/TD]
[TD="align: right"]23.9[/TD]
[TD="align: right"]14.3[/TD]
[TD="align: right"]18.2[/TD]
[TD="align: right"]24.8[/TD]
[TD="align: right"]22.7[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Angela Simmons[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]27.6[/TD]
[TD="align: right"]23.4[/TD]
[TD="align: right"]21.9[/TD]
[TD="align: right"]20.4[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]Martha Perry[/TD]
[TD="align: right"]11.7[/TD]
[TD="align: right"]13.9[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]15.7[/TD]
[TD="align: right"]28.2[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Lawrence Lewis[/TD]
[TD="align: right"]23.6[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]19.9[/TD]
[TD="align: right"]18.4[/TD]
[TD="align: right"]20.1[/TD]
[TD="align: right"]1[/TD]
[/TR]
</tbody>[/TABLE]

It is the final column I am seeking a formula for. I have entered the expected result.

 

[Special-K99]

Why is Roger Wards consecutive week > = 19 set to 0?
Week2 and Week3 are both > 19 so shouldnt the result be 2 ?

For that matter Chery'ls should be 3 not 4

 

[Special-K99]

Try this

in h2

=MAX(FREQUENCY(IF(B2:G2>=19,COLUMN(B2:G2)),IF(B2:G2<19,COLUMN(B2:G2))))
Array formula, use Ctrl-Shift-Enter

But dont ask me how it works, cribbed from various sources

 

[myredroom]

oops, sorry my mistake!

 

[myredroom]

Correction, Roger Ward is zero because the preceding week didn't reach target. I fired off my reply a bit quick there!

 

[myredroom]

Try this

in h2

=MAX(FREQUENCY(IF(B2:G2>=19,COLUMN(B2:G2)),IF(B2:G2<19,COLUMN(B2:G2))))
Array formula, use Ctrl-Shift-Enter

But dont ask me how it works, cribbed from various sources

This gives the following results:

[TABLE="class: grid, width: 147"]
<tbody>[TR]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[/TR]
</tbody>[/TABLE]

The last 4 results are correct, the first 2 not. I can't see a pattern though.

 

[Special-K99]

So Cheryl should still be 3.

Not sure I can help you here with that additional "reset to 0 if target no longer becomes achieved"

I'll have a think but am not holding out much hope in working that out...

 

[Roger Govier]

Hi
I think if you modify K-99's formula with a leading IF test, you will get the result you want.

Also needs to be array entered with CSE (unless you are on the Insider Fast version fo Office 365 with Dynamic Arrays)

=IF(G2<19,0,MAX(FREQUENCY(IF(B2:G2>=19,COLUMN(B2:G2)),IF(B2:G2<19,COLUMN(B2:G2)))))

 

[myredroom]

Hi
I think if you modify K-99's formula with a leading IF test, you will get the result you want.

Also needs to be array entered with CSE (unless you are on the Insider Fast version fo Office 365 with Dynamic Arrays)

=IF(G2<19,0,MAX(FREQUENCY(IF(B2:G2>=19,COLUMN(B2:G2)),IF(B2:G2<19,COLUMN(B2:G2)))))

This also hasn't achieved the desired results.:
[TABLE="class: grid, width: 64"]
<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64, align: right"] 2


[/TD]
[/TR]
[TR]
[TD="align: right"] 0



[/TD]
[/TR]
[TR]
[TD="align: right"] 1



[/TD]
[/TR]
[TR]
[TD="align: right"] 0



[/TD]
[/TR]
[TR]
[TD="align: right"] 1



[/TD]
[/TR]
[TR]
[TD="align: right"] 1



[/TD]
[/TR]
</tbody>[/TABLE]

Perhaps I've not been clear. I'm trying count backwards from now, the number of weeks until a result of less than 19 is found. Ignoring any zero entries. Maybe I'll just write a user defined function instead!