# Average Streak Based on Criteria Ignoring Blanks

#### WhoPaysTheBagels

##### New Member
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
[FONT=&quot]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.[/FONT]

#### Domenic

##### MrExcel MVP
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!

#### WhoPaysTheBagels

##### New Member
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.

#### Domenic

##### MrExcel MVP
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

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

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?

#### WhoPaysTheBagels

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

<tbody>
</tbody>

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

#### Domenic

##### MrExcel MVP
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.

#### WhoPaysTheBagels

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

<tbody>
</tbody>

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

#### Domenic

##### MrExcel MVP
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: