Average Streak Based on Criteria Ignoring Blanks

WhoPaysTheBagels

New Member
Joined
Aug 27, 2019
Messages
4
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]
 

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,092
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
Joined
Aug 27, 2019
Messages
4
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
Joined
Mar 10, 2004
Messages
19,092
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
Joined
Aug 27, 2019
Messages
4
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
Joined
Mar 10, 2004
Messages
19,092
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
Joined
Aug 27, 2019
Messages
4
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
Joined
Mar 10, 2004
Messages
19,092
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:

Forum statistics

Threads
1,089,265
Messages
5,407,258
Members
403,131
Latest member
Lewas2019

This Week's Hot Topics

Top