Average Streak Based on Criteria Ignoring Blanks

W

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]
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Sort by date Sort by votes
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!
 
Upvote 0
Domenic said:
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!
Click to expand...

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.
 
Upvote 0
I would suggest that you mark an absence with some indicator, for example an "A". Then, let's say we have the following data...

[TABLE="width: 64"]
<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl63, width: 64, align: center"]0[/TD]
[/TR]
[TR]
[TD="class: xl63, align: center"]1[/TD]
[/TR]
[TR]
[TD="class: xl63, align: center"]A[/TD]
[/TR]
[TR]
[TD="class: xl63, align: center"]1[/TD]
[/TR]
[TR]
[TD="class: xl63, align: center"]0[/TD]
[/TR]
[TR]
[TD="class: xl63, align: center"]1[/TD]
[/TR]
[TR]
[TD="class: xl63, align: center"]1[/TD]
[/TR]
[TR]
[TD="class: xl63"] [/TD]
[/TR]
[TR]
[TD="class: xl63"] [/TD]
[/TR]
[TR]
[TD="class: xl63"] [/TD]
[/TR]
</tbody>[/TABLE]

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?
 
Upvote 0
Domenic said:
I would suggest that you mark an absence with some indicator, for example an "A". Then, let's say we have the following data...

[TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl63, width: 64, align: center"]0[/TD]
[/TR]
[TR]
[TD="class: xl63, align: center"]1[/TD]
[/TR]
[TR]
[TD="class: xl63, align: center"]A[/TD]
[/TR]
[TR]
[TD="class: xl63, align: center"]1[/TD]
[/TR]
[TR]
[TD="class: xl63, align: center"]0[/TD]
[/TR]
[TR]
[TD="class: xl63, align: center"]1[/TD]
[/TR]
[TR]
[TD="class: xl63, align: center"]1[/TD]
[/TR]
[TR]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"][/TD]
[/TR]
</tbody>[/TABLE]

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?
Click to expand...


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
 
Upvote 0
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.
 
Upvote 0
Domenic said:
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.
Click to expand...

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.

[TABLE="width: 25"]
<tbody>[TR]
[TD]1[/TD]
[/TR]
[TR]
[TD]1[/TD]
[/TR]
[TR]
[TD]1[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[/TR]
[TR]
[TD]0[/TD]
[/TR]
[TR]
[TD]0[/TD]
[/TR]
[TR]
[TD]1[/TD]
[/TR]
[TR]
[TD]1[/TD]
[/TR]
[TR]
[TD]1[/TD]
[/TR]
[TR]
[TD]1[/TD]
[/TR]
[TR]
[TD]1[/TD]
[/TR]
[TR]
[TD]0[/TD]
[/TR]
[TR]
[TD]0[/TD]
[/TR]
[TR]
[TD]0[/TD]
[/TR]
[TR]
[TD]1[/TD]
[/TR]
[TR]
[TD]1[/TD]
[/TR]
[TR]
[TD]1[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[/TR]
[TR]
[TD]2[/TD]
[/TR]
[TR]
[TD]2[/TD]
[/TR]
[TR]
[TD]2[/TD]
[/TR]
[TR]
[TD]2[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[/TR]
[TR]
[TD]1[/TD]
[/TR]
[TR]
[TD]1[/TD]
[/TR]
[TR]
[TD]1[/TD]
[/TR]
[TR]
[TD]2[/TD]
[/TR]
[TR]
[TD]2[/TD]
[/TR]
[TR]
[TD]2[/TD]
[/TR]
</tbody>[/TABLE]


0 example- (2+3)/2=2.5
1 example- (4+5+4+3)/4= 4
2 example- (5+3)/2= 4
 
Upvote 0
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:
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
Cell with 0 value showing blank
2
Replies
15
Views
803
mhessnm
M
D
  • Question Question
VBA Code to Apply an AVERAGE of a Range on Cells as New Records are added Via a User Form
Replies
2
Views
97
Denny57
D
Z
  • Solved
VBA: Advanced Filter Limitations
Replies
3
Views
832
zero269
Z
P
  • Question Question
OR function that ignores blanks
Replies
1
Views
622
KRice
K
K
  • Solved
Average Based on Criteria
Replies
3
Views
388
Fluff
Fluff

Forum statistics

Threads
1,224,820
Messages
6,181,160
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top