Results 1 to 9 of 9

Thread: Find consecutive cells above a certain value, ignoring zeroes.
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jun 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Find consecutive cells above a certain value, ignoring zeroes.

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

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

  2. #2
    Board Regular
    Join Date
    Nov 2006
    Location
    London
    Posts
    8,196
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Find consecutive cells above a certain value, ignoring zeroes.

    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 by Special-K99; Jul 3rd, 2019 at 07:06 AM.

  3. #3
    Board Regular
    Join Date
    Nov 2006
    Location
    London
    Posts
    8,196
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Find consecutive cells above a certain value, ignoring zeroes.

    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 by Special-K99; Jul 3rd, 2019 at 07:35 AM.

  4. #4
    New Member
    Join Date
    Jun 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Find consecutive cells above a certain value, ignoring zeroes.

    oops, sorry my mistake!

  5. #5
    New Member
    Join Date
    Jun 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Find consecutive cells above a certain value, ignoring zeroes.

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

  6. #6
    New Member
    Join Date
    Jun 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Find consecutive cells above a certain value, ignoring zeroes.

    Quote Originally Posted by Special-K99 View Post
    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

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

  7. #7
    Board Regular
    Join Date
    Nov 2006
    Location
    London
    Posts
    8,196
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Find consecutive cells above a certain value, ignoring zeroes.

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

  8. #8
    Board Regular
    Join Date
    Jun 2002
    Location
    Abergavenny, Wales, UK
    Posts
    259
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Find consecutive cells above a certain value, ignoring zeroes.

    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 by Roger Govier; Jul 3rd, 2019 at 08:22 AM.
    --
    Roger Govier
    Technology 4 U

  9. #9
    New Member
    Join Date
    Jun 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Find consecutive cells above a certain value, ignoring zeroes.

    Quote Originally Posted by Roger Govier View Post
    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



    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

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •