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

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,106
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,106
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,106
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,106
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:

Watch MrExcel Video

Forum statistics

Threads
1,090,239
Messages
5,413,252
Members
403,468
Latest member
GRamos

This Week's Hot Topics

Top