Find consecutive cells above a certain value, ignoring zeroes.

myredroom

New Member
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:

week 1week 2week 3week 4week 5week 6consecutive weeks >=19
Cheryl Wood28.60028.527.931.54
Roger Ward10.422.827.711.9016.90
Nancy Butler12.723.914.318.224.822.72
Angela Simmons027.623.421.920.404
Martha Perry11.713.90015.728.21
Lawrence Lewis23.60019.918.420.11

<tbody>
</tbody>

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

Special-K99

Well-known Member
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
 
Last edited:

Special-K99

Well-known Member
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
 
Last edited:

myredroom

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

myredroom

New Member
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:

3
2
2
4
1
1

<tbody>
</tbody>

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

Special-K99

Well-known Member
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

Active Member
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)))))
 
Last edited:

myredroom

New Member
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.:
2


0



1



0



1



1




<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>
</tbody>

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!
 

Some videos you may like

This Week's Hot Topics

  • Get External Data (long shot question!)
    This is likely a long shot but I am wondering if it is at all possible for Excel to somehow 'change' the contents of a URL that is being linked to...
  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • Cell Formatting
    Good Morning, I need to format a few different cells in the following manners: A1 has to always add a colon (:) after whatever is typed in by a...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • Workbook_Change stopped working !
    I am working on an app to speed up & automate processing of Credit Cards statements. After data is input from a CSV file, it is presented to the...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
Top