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

1. ## 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. ## 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. ## Re: Average Streak Based on Criteria Ignoring Blanks

Originally Posted by Domenic
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. ## 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. ## Re: Average Streak Based on Criteria Ignoring Blanks

Originally Posted by Domenic
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. ## 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. ## Re: Average Streak Based on Criteria Ignoring Blanks

Originally Posted by Domenic
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. ## 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!