Results 1 to 8 of 8

Thread: Average Streak Based on Criteria Ignoring Blanks
Thanks Thanks: 0 Likes Likes: 0

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

    Default Average Streak Based on Criteria Ignoring Blanks

    I'm creating a program for my job to evaluate the behavior management system with four levels (0,1,2,3,4). I am stuck on finding a formula to calculate the average streak within given time periods (monthly, quarterly, yearly, etc.) based on criteria (grade). The biggest issue I am having is if there is a blank between the two sets of data, it reads it as two separate sets when it should be read as one. If there is a blank it is due to a student absence. For example, I would like it to read the four 1's as one streak.


    0
    1
    1
    1

    1
    0
    1
    1

    The formula I was previously using is
    AVERAGE(IF(FREQUENCY(IF(FormulasLevelYear=0,ROW(FormulasLevelYear)),IF(FormulasLevelYear<>0,ROW(FormulasLevelYear)))>0,FREQUENCY(IF(FormulasLevelYear=0,ROW(FormulasLevelYear)),IF(FormulasLevelYear<>0,ROW(FormulasLevelYear)))))

    however, it does not exclude the blanks as I hoped.

  2. #2
    MrExcel MVP
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    18,855
    Post Thanks / Like
    Mentioned
    35 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Average Streak Based on Criteria Ignoring Blanks

    Try...

    Code:
    =AVERAGE(IF(FREQUENCY(IF((LEN(FormulasLevelYear)=0)+(FormulasLevelYear<>0)>0,ROW(FormulasLevelYear)),IF(LEN(FormulasLevelYear)>0,IF(FormulasLevelYear=0,ROW(FormulasLevelYear))))>0,FREQUENCY(IF((LEN(FormulasLevelYear)=0)+(FormulasLevelYear<>0)>0,ROW(FormulasLevelYear)),IF(LEN(FormulasLevelYear)>0,IF(FormulasLevelYear=0,ROW(FormulasLevelYear))))))
    ...confirmed with CONTROL+SHIFT+ENTER.

    Hope this helps!

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

    Default Re: Average Streak Based on Criteria Ignoring Blanks

    Quote Originally Posted by Domenic View Post
    Try...

    Code:
    =AVERAGE(IF(FREQUENCY(IF((LEN(FormulasLevelYear)=0)+(FormulasLevelYear<>0)>0,ROW(FormulasLevelYear)),IF(LEN(FormulasLevelYear)>0,IF(FormulasLevelYear=0,ROW(FormulasLevelYear))))>0,FREQUENCY(IF((LEN(FormulasLevelYear)=0)+(FormulasLevelYear<>0)>0,ROW(FormulasLevelYear)),IF(LEN(FormulasLevelYear)>0,IF(FormulasLevelYear=0,ROW(FormulasLevelYear))))))
    ...confirmed with CONTROL+SHIFT+ENTER.

    Hope this helps!
    Thanks for replying! It gave me a result of 91 when I only have 1 zero in my data? And 33 when it should have been 8 when swapping out zero for 1. I was looking to find the average streaks within those numbers. So average streak of 0, average streak of 1, etc.

  4. #4
    MrExcel MVP
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    18,855
    Post Thanks / Like
    Mentioned
    35 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Average Streak Based on Criteria Ignoring Blanks

    I would suggest that you mark an absence with some indicator, for example an "A". Then, let's say we have the following data...

    0
    1
    A
    1
    0
    1
    1

    You can use the following formula, which also needs to be confirmed with CONTROL+SHIFT+ENTER..

    Code:
    =AVERAGE(IF(FREQUENCY(IF(FormulasLevelYear<>0,ROW(FormulasLevelYear)),IF(LEN(FormulasLevelYear)>0,IF(FormulasLevelYear=0,ROW(FormulasLevelYear))))>0,FREQUENCY(IF(FormulasLevelYear<>0,ROW(FormulasLevelYear)),IF(LEN(FormulasLevelYear)>0,IF(FormulasLevelYear=0,ROW(FormulasLevelYear))))))
    In this case, the formula would return (3 + 2)/2 = 2.5. Would this work for you?

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

    Default Re: Average Streak Based on Criteria Ignoring Blanks

    Quote Originally Posted by Domenic View Post
    I would suggest that you mark an absence with some indicator, for example an "A". Then, let's say we have the following data...

    0
    1
    A
    1
    0
    1
    1

    You can use the following formula, which also needs to be confirmed with CONTROL+SHIFT+ENTER..

    Code:
    =AVERAGE(IF(FREQUENCY(IF(FormulasLevelYear<>0,ROW(FormulasLevelYear)),IF(LEN(FormulasLevelYear)>0,IF(FormulasLevelYear=0,ROW(FormulasLevelYear))))>0,FREQUENCY(IF(FormulasLevelYear<>0,ROW(FormulasLevelYear)),IF(LEN(FormulasLevelYear)>0,IF(FormulasLevelYear=0,ROW(FormulasLevelYear))))))
    In this case, the formula would return (3 + 2)/2 = 2.5. Would this work for you?

    I added an indicated as you said and I'm still not getting an accurate average for any of them. Right now, even with no 3's in my data, I'm getting a result of 182 when I change the 0s to 3s

  6. #6
    MrExcel MVP
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    18,855
    Post Thanks / Like
    Mentioned
    35 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Average Streak Based on Criteria Ignoring Blanks

    Are your blank cells a result of a formula that returns an empty string ("") ? If so, try the following formula instead...

    Code:
    =AVERAGE(IF(FREQUENCY(IF(LEN(FormulasLevelYear)>0,IF(FormulasLevelYear<>0,ROW(FormulasLevelYear))),IF(LEN(FormulasLevelYear)>0,IF(FormulasLevelYear=0,ROW(FormulasLevelYear))))>0,FREQUENCY(IF(LEN(FormulasLevelYear)>0,IF(FormulasLevelYear<>0,ROW(FormulasLevelYear))),IF(LEN(FormulasLevelYear)>0,IF(FormulasLevelYear=0,ROW(FormulasLevelYear))))))
    If this still doesn't return the desired result, please post a representative set of data, along with a few examples of your expected results.

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

    Default Re: Average Streak Based on Criteria Ignoring Blanks

    Quote Originally Posted by Domenic View Post
    Are your blank cells a result of a formula that returns an empty string ("") ? If so, try the following formula instead...

    Code:
    =AVERAGE(IF(FREQUENCY(IF(LEN(FormulasLevelYear)>0,IF(FormulasLevelYear<>0,ROW(FormulasLevelYear))),IF(LEN(FormulasLevelYear)>0,IF(FormulasLevelYear=0,ROW(FormulasLevelYear))))>0,FREQUENCY(IF(LEN(FormulasLevelYear)>0,IF(FormulasLevelYear<>0,ROW(FormulasLevelYear))),IF(LEN(FormulasLevelYear)>0,IF(FormulasLevelYear=0,ROW(FormulasLevelYear))))))
    If this still doesn't return the desired result, please post a representative set of data, along with a few examples of your expected results.
    Thank you so much for taking your time to help me and your patience.

    The blank cells are a result of that formula. However, still having issues? When replacing the 0 with 1,2, or 3 I get a DIV/0 error.

    1
    1
    1
    1
    0
    0
    1
    1
    1
    1
    1
    0
    0
    0
    1
    1
    1
    1
    2
    2
    2
    2
    2
    1
    1
    1
    2
    2
    2


    0 example- (2+3)/2=2.5
    1 example- (4+5+4+3)/4= 4
    2 example- (5+3)/2= 4

  8. #8
    MrExcel MVP
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    18,855
    Post Thanks / Like
    Mentioned
    35 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Average Streak Based on Criteria Ignoring Blanks

    For consecutive 0's, try...

    Code:
    =AVERAGE(IF(FREQUENCY(IF(LEN(FormulasLevelYear)>0,IF(FormulasLevelYear=0,ROW(FormulasLevelYear))),IF(LEN(FormulasLevelYear)>0,IF(FormulasLevelYear<>0,ROW(FormulasLevelYear))))>0,FREQUENCY(IF(LEN(FormulasLevelYear)>0,IF(FormulasLevelYear=0,ROW(FormulasLevelYear))),IF(LEN(FormulasLevelYear)>0,IF(FormulasLevelYear<>0,ROW(FormulasLevelYear))))))
    For consecutive 1's, try...

    Code:
    =AVERAGE(IF(FREQUENCY(IF(FormulasLevelYear=1,ROW(FormulasLevelYear)),IF(LEN(FormulasLevelYear)>0,IF(FormulasLevelYear<>1,ROW(FormulasLevelYear))))>0,FREQUENCY(IF(FormulasLevelYear=1,ROW(FormulasLevelYear)),IF(LEN(FormulasLevelYear)>0,IF(FormulasLevelYear<>1,ROW(FormulasLevelYear))))))
    For conseutive 2's, replace the 1's with 2's.

    Note that the formulas need to be confirmed with CONTROL+SHIFT+ENTER. Also, the formulas are based on the last set of sample data that you provided.

    Hope this helps!
    Last edited by Domenic; Sep 2nd, 2019 at 10:21 AM.

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
  •