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="]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]
0
1
1
1
1
0
1
1
The formula I was previously using is
[FONT="]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]